Anexar consultas con M.

Para el siguiente ejercicio, tenemos una seria de provincias, cada una de ellas en una hoja, todas tienen la misma estructura, y, son ventas de productos en diferentes centros comerciales, y, fechas.

Queremos anexar todas las hojas en una sola, esto ya lo hemos hecho anexando consultas, pero vamos a hacerlo de otra forma.

Tengo un libro abierto, donde tengo la ruta del archivo con el que vamos a trabajar.

Voy a seleccionar la celda, voy a la pestaña de datos, dentro de obtener y transformar, hago clic en de una tabla o rango.

En pasos aplicados, vemos el paso origen, encabezados promovidos, y, tipo cambiado.

Borro los dos últimos pasos, quedándome solo con el paso origen.

Si miramos en la barra de fórmulas, vemos el nombre de rango que le he asignado a la celda, y, esta entregando la tabla.

= Excel.CurrentWorkbook(){[Name="ruta"]}[Content]

Necesitamos el primer registro, ya sabemos que en Power Query, el primer registro es el numero cero, por lo que usamos el índice posicional, y, entre llaves ponemos cero.

= Excel.CurrentWorkbook(){[Name="ruta"]}[Content]{0}

Aceptamos, y, obtengo el registro.

Ahora, tenemos que extraer la columna, para ello, entre corchetes, operador de acceso de campo, ponemos Column1.

= Excel.CurrentWorkbook(){[Name="ruta"]}[Content]{0}[Column1]

Ya tenemos la ruta del archivo.

Lo siguiente es acceder al contenido, para ello, vamos a agregar un nuevo paso, para ello, hacemos clic en:

Aparece el último paso.

Ahora, después del signo igual, vamos a usar la función File.Contents, que devuelve el contenido de un archivo, esta función tiene un único argumento que es path, en este caso, dicho argumento es el último paso.

= Excel.Workbook(File.Contents(Origen), null, true)

Aceptamos, y, vemos que agrego el libro de trabajo, en otras palabras, he obtenido acceso al archivo externo.

El segundo argumento se establece en null, quiero decir que no promueve los encabezados.

En la columna Kind, vemos que tenemos sheet, y, nombredefinido, este ultimo no lo necesitamos.

Vamos a desplegar el filtro de dicha columna y desmarcamos DefineName, porque solo queremos importar objetos de la hoja de cálculo, y, aceptamos.

Si nos fijamos en la barra de fórmulas, vemos para cada elemento de Kind que sea igual a Sheet.

= Table.SelectRows(Personalizado1, each ([Kind] = "Sheet"))

Si quisiera de la columna name, quitar alguna provincia, agrego las siguientes líneas, que quiere decir, dejar en la columna Kind, los registros llamados Sheet, y, además traer las provincias que sean diferente a Albacete.

= Table.SelectRows(Personalizado1, each ([Kind] = "Sheet") and ([Name] <> "Albacete"))

Esto era solo un ejemplo, por lo que voy a borrar el paso.

En cada fila, tenemos una tabla, si hacemos clic en alguna de ellas, y, miramos en la parte inferior, veremos la tabla, y, vemos que los encabezados no están promovidos.

Lo siguiente va a ser traer la primera tabla, para ello, voy a agregar un nuevo paso, y, después del nombre del primer paso, voy a usar el índice posicional, y, pongo como valor cero.

= #"Filas filtradas"{0}

Con esto consigo acceder al primer registro.

Si vamos cambiando el valor, vemos como accedemos al registro indicado.

Pero, lo que quiero es acceder a la primera tabla, la de Albacete, que es el índice posicional cero, para ello, después del índice posicional, y, entre corchetes ponemos el nombre de la columna a recuperar (Data).

= #"Filas filtradas"{0}[Data]

Ya tenemos la tabla para Albacete.

Promovemos el encabezado, para ello, vamos a la pestaña de transformar, y, hacemos clic en usar la primera fila como encabezado.

Lo siguiente que vamos a hacer es ir al editor avanzado, para ello, podemos ir desde la pestaña de inicio.

Los únicos pasos que vamos a necesitar son desde promover encabezados.

Por lo que los voy a borrar.

El problema es que, en la primera línea, apunta al último paso.

De alguna manera, debemos tener acceso a todas las tablas, la forma de hacerlo es crear una función personalizada, por lo que variable, lo haremos encima de LET, para ello, entre paréntesis ponemos el nombre de la variable, seguido de igual, y, mayor que.

Ahora, sustituimos el ultimo paso por la variable.

Esto quiere decir que el valor de la variable, son todas las líneas que hay por debajo de ella, que es una función personalizada, que podemos usar una y otra vez.

Hacemos clic en listo, y, nos aparece la ventana de parámetro, donde debemos de introducir dicho parámetro.

Voy a volver al paso dejar sheet, voy a la pestaña agregar columna, y, hago clic en invocar función personalizada.

En la ventana que se abre, como nombre voy a poner invocar, en consulta de función, selecciono la consulta crear variable, y, en la ventana variable, elijo data.

Aceptamos.

Aparece una nueva columna con tablas, que, si hacemos clic en alguna de ellas, vemos la tabla, pero con el encabezado promovido.

Agregamos un nuevo paso, al final de la expresión, entre corchetes, ponemos el nombre que pusimos al crear la columna que indicamos.

= #"Función personalizada invocada"[invocar]

Aceptamos, y, tenemos una lista de tablas.

Ahora, usamos la función Table.Combine, para combinar todas las tablas.

= Table.Combine(#"Función personalizada invocada"[invocar])

Ya tenemos todas nuestras provincias en una única tabla.

Cerramos y cargamos como una conexión.

En la ventana de la derecha, en consultas y conexiones, hacemos clic con botón alternativo de ratón sobre la consulta modelo final, y, seleccionamos cargar en.

Se abre la ventana de importar datos, seleccionamos tabla, y, hoja de cálculo nueva.

Aceptamos.

Ya tenemos nuestra tabla con todas las provincias en nuestra hoja de cálculo.

Es probable, que deban de cambiar la ruta de acceso al archivo, para ello, hacen clic con boton secundario del raton sobre le archivo, y, seleccionáis propiedades, en la ventana general, copian la ruta.