En este ejemplo, vamos a volver a usar Excel.CurrentWorkbook, para traernos las tablas del libro activo, es el mismo que para el ejemplo anterior, pero alguien, en la hoja Sevilla, ha realizado un filtro avanzado por el centro comercial Carrefour.
En la hoja Córdoba, se ha creado una lista única de centros, al cual se le ha dado un nombre de rango, centros_cordoba.
Cuando realizamos un filtro avanzado, se crean dos nombres definidos, o, nombres de rangos, uno llamado área de extracción, y, otro llamado criterios.
Si desplegamos el cuadro de nombres, podemos verlos.
Área de extracción, hace referencia al encabezado de los datos filtrados, y, criterios, al encabezado y criterio, o, criterios.
Cuando vayamos a usar Excel.CurrentWorkbook, estos nombres definidos, también se importan.
Vamos a la pestaña de fórmulas, dentro del grupo nombres definidos, hacemos clic en administrador de nombres, también, podemos pulsar CTRL más F3.
Podemos ver los nombres área de impresión, criterios, y, el nombre de rango creado, centros_cordoba.
Pero, no vemos los datos filtrados, entonces, queremos traer solo las tablas, vamos a la pestaña de datos, dentro del grupo obtener y transformar, desplegamos obtener datos, y, vamos hasta de otras fuentes.
En la ventana de la derecha, seleccionamos consulta en blanco.
Le cambio el nombre a la consulta.
En la barra de fórmulas, ponemos:
Obtenemos las tablas del libro activo.
Si nos fijamos en la columna Content, vemos que tenemos diferentes tablas, también, tenemos una fila con un error, que hace referencia a los datos filtrados.
Este error no entra como una tabla.
Cada vez que realicemos un filtro avanzado, se crea este nombre oculto.
Si hacemos clic en la fila de criterios, en la parte inferior izquierda, vemos los datos que lo forman, tiene una sola columna, llamada Column1, y, vemos el encabezado que hemos usado, Centro, y, el criterio.
Si hacemos clic en área de extracción, vemos las 7 columnas, llamadas desde Column1 hasta Column7, y, en cada columna tenemos el nombre de cada encabezado.
Vemos que los nombres definidos, área de extracción, y, criterios, todos empiezan por Column1, entonces, debemos de añadir una nueva columna, para extraer Column1.
En Power Query, contamos con gran cantidad de funciones para tablas.
En un navegador, escribo:
Selecciono el siguiente enlace:
Si miramos a la izquierda, vemos todo tipo de funciones.
Pero, antes, voy a quitar el error.
Si nos fijamos en la parte inferior, vemos de donde proviene el error, y el nombre de la fila filtrada.
Pues, la selecciono.
CTRL mas C para copiar, desplegamos filtro, vamos hasta filtros de texto, y, en la ventana de la derecha, seleccionamos no contiene.
Se abre la ventana filtrar filas, y, en la ventana a la derecha de la ventana no es igual a, pulsamos CTRL mas V, para pegar.
Aceptamos.
Y ya no tenemos el error.
Ahora, tenemos que agregar esa columna adicional, para ello, vamos a la pestaña agregar columna, dentro del grupo general, hacemos clic en columna personalizada.
Como nombre le voy a poner agregar columna.
En la ventana de columna personalizada, ponemos:
Debemos de poner Table, con la primera letra en mayúsculas, en caso contrario, no lo reconocerá.
Abrimos un paréntesis, y, seleccionamos la columna Content, y, cerramos paréntesis.
Con esto, tenemos acceso a la columna Content, quiere decir que buscara lo que indiquemos en dicha columna, hacemos clic en aceptar.
Nos va a devolver una lista con los nombres de columnas.
Si hacemos clic en el primer registro, en la parte inferior, podemos ver los encabezados de dicha tabla.
Si hacemos clic en criterios, vemos que está formado por una columna, llamada Column1.
Si hacemos clic en área de extracción, vemos que esta formado por cuatro columnas.
Bien, debemos de quedarnos con la columna 1, voy a hacer doble clic en pasos aplicados en el paso personalizada agregada.
Accedemos a la ventana de columna personalizada, al final de la expresión, abrimos una llave.
En Excel, usamos las llaves para crear una constante de matriz, aquí la usaremos para indicar la columna que queremos ver, pero hay que tener en cuenta, que cuando nos vamos a referir a una columna, por ejemplo, a la columna 1, no tenemos que poner el número 1, porque se empieza a contar desde cero, es decir, que para referirnos a la columna 1, debemos de poner el valor cero, esto, también sucede con la función DESREF en Excel, pues, ponemos el numero cero, y, cerramos la llave.
Hacemos clic en aceptar, y, obtenemos la primera columna de cada tabla.
Ahora, tengo que deshacerme de las columnas llamadas Column1, para ello, desplegamos filtro, vamos hasta filtros de texto.
En la ventana de la derecha, elegimos no contiene.
Se abre la ventana filtrar filas, y, en la ventana de la derecha de la ventana no contiene, ponemos Column1.
Aceptamos.
Ya tenemos las tablas que necesitamos.
Elimino la columna agregar columna, porque ya no la necesito, para ello, hago clic con botón alternativo de ratón sobre la columna, y, elegimos quitar.
Voy a llamar a la columna Name, como provincia.
Igual que en el ejemplo anterior, en la columna provincia, voy a reemplazar _Ventas por nada, para quedarme con el nombre de la provincia, para ello, teniendo la columna seleccionada, vamos a la pestaña de inicio, dentro del grupo transformar, hacemos clic en reemplazar valores.
Se abre la ventana reemplazar los valores, en la ventana valor que buscar, ponemos, _Ventas, y, en la ventana reemplazar con, no ponemos nada, porque queremos que se sustituya por nada.
Aceptamos.
Ya lo tenemos.
Ya sabemos que ahora, cerramos y cargamos, se cargaran todas las tablas, además, de esta que vamos a crear ahora, por lo que voy a adelantarme, voy a copiar el nombre de la consulta Datos, con CTRL mas C, despliego filtro, voy hasta filtros de texto.
En la ventana de la derecha, seleccionamos no contiene.
En la ventana derecha a la ventana no contiene, pegamos con CTRL más V.
Aceptamos.
Ahora, vamos a ampliar, para ello, hacemos clic en:
Desmarcamos usar el nombre de columna original como prefijo, y, aceptamos.
Ya tenemos las tablas solo de las provincias anexadas.
Vemos que el tipo de datos no ha sido detectado, para ello, pulsamos CTRL mas A, para seleccionar todo el modelo.
Vamos a la pestaña transformar, dentro de cualquier columna, hacemos clic en detectar tipo de datos.
Vemos que en la columna fecha, nos aparece la hora, pero no tenemos hora.
Pues, hacemos clic en el siguiente icono, y, seleccionamos fecha.
Seleccionamos la columna cantidad, vamos a la pestaña de inicio, dentro del grupo transformar, desplegamos tipo de datos, y, seleccionamos número entero.
Hacemos lo mismo para la columna de total.
Cerramos y cargamos.
En la ventana de la derecha, consultas y conexiones, vemos un total de 450 filas.
Si actualizamos, vemos que no cambia el recuento de filas, porque esta cargando, solamente, las provincias.
Si desplegamos filtro de la columna provincia, vemos solo las provincias.
Vamos a ver el código M que ha generado, para ello, volvemos a Power Query, desde la pestaña inicio, hacemos clic en editor avanzado.
Toda transformación comienza con la función LET.
Tenemos la función Excel.CurrentWorkBook.
Vemos las filas filtradas, si leemos la línea, vemos que la variable origen, es decir, todas las tablas, han sido filtradas por las que la columna Name, que no contiene FilterDatabase.
En la siguiente línea, añade una columna personalizada a filas filtradas, es decir, a las tablas que no contienen Filterdatabase, dicha columna nos devuelve la columna cero, es decir, la uno, de la columna Content.
La siguiente línea, selecciona las filas de la columna personalizada que no contiene Column1.
Como son muchas líneas, solo vamos a ver estas.
La declaración In, siempre contiene la última línea.
Comentarios