Power Query. Parte 7

Jaime Franco Jimenez
31 may 2022
10 Min. de lectura

Veamos un ejemplo más práctico.

Veamos un primer ejemplo. Transponer.

Vamos a traernos, el siguiente archivo.

Vemos que hay columnas, y, filas de más.

Vamos a seleccionar las dos columnas de más, hacemos clic con botón alternativo de ratón, sobre uno de los encabezados, y, seleccionamos quitar columnas.

Ahora, seleccionamos todas las columnas, en uno de los encabezados, vamos a la pestaña de inicio, dentro del grupo reducir filas, desplegamos quitar filas, y, seleccionamos quitar filas en blanco.

Ya tenemos el modelo preparado, donde tenemos ingresos y gastos de diferentes años.

Si quisiera ver los años en vertical, podía copiar y pegar transponiendo, es decir, que lo que este en vertical lo pondrá en horizontal, y, lo que está en horizontal, lo pondrá en vertical.

Vemos que los encabezados, no están promovidos, vamos a la pestaña de transformar, dentro del grupo tabla, hacemos clic en, usar la primera fila como encabezado.

Lo siguiente va a ser, desde la misma pestaña transformar, vamos a transponer, pues es lo que queremos.

Vemos que se ha transpuesto, pero que el encabezado de los años ha desaparecido.

Esto es porque cuando vamos a transponer, no podemos usar la primera fila como encabezado, vamos a ir a la ventana de pasos aplicados, y, borramos el paso de promover encabezados, y el de transponer, para ello, simplemente hacemos clic en la X a la izquierda del nombre del paso.

Es ahora, cuando tenemos que transponer, pues, vamos de nuevo a la pestaña transformar, y, hacemos clic en transponer.

Ahora, si promovemos el encabezado.

Veamos otro ejemplo, quizás algo más complicado.

Vamos a traernos, el siguiente archivo.

Vemos en la primera fila, aparece NULL.

Pues, vamos a la pestaña inicio, dentro del grupo reducir filas, desplegamos quitar filas, y, elegimos quitar filas superiores.

Se abre la ventana, quitar filas superiores, pues, en la ventana, numero de filas, ponemos 1 y aceptamos.

A la derecha también tenemos una columna sobrante.

Pues la seleccionamos y con botón alternativo de ratón, elegimos quitar.

Por seguridad, también le vamos a decir que nos quite las filas en blanco, para ello, desde la pestaña de inicio, desplegamos quitar filas, y, seleccionamos quitar filas en blanco.

Bien, tenemos los años y los trimestres de cada año.

Queremos que aparezca, por ejemplo, 2018 en cada trimestre, igual para el resto de los años, pero con power Query no podemos copiar, pues vamos a hacer lo siguiente.

Lo primero que vamos a hacer es transponer, para ello, vamos a la pestaña transformar, dentro del grupo tabla, hacemos clic en transponer.

Ahora, tenemos los años en vertical, pero podemos ver que solo aparece una vez el año para el trimestre 1, para el resto aparece NULL.

Ya hemos visto, anteriormente, Fill Down, que toma una columna y recorre los valores de esta para rellenar los valores NULL en las filas siguientes hasta que encuentre un nuevo valor, pues es lo que vamos a hacer, entonces, seleccionamos la columna de los años, podemos hacerlo de dos formas, una ya la sabemos, que es haciendo clic con el botón secundario del ratón sobre la columna, nos movemos hasta rellenar, y, elegimos abajo, otra de ellas, es ir a la pestaña transformar, dentro del grupo cualquiera columna, vemos rellenar, desplegamos, y, elegimos abajo.

Con esto, ya tenemos rellenado los años para toda la columna.

Volvemos a transponer.

Ahora, vamos a unir el año con el trimestre, para ello, volvemos a transponer.

Seleccionamos la columna de los años y de los trimestres.

En la pestaña transformar, dentro del grupo, columna de texto, hacemos clic en combinar consultas.

Se abre la ventana, combinar consultas, en la ventana, separador, elegimos separador la coma, en la ventana, nuevo nombre de columna, no tenemos que hacer nada.

Aceptamos, y, vemos una columna llamada, combinada, donde se ha unido el año con el trimestre, separado por una coma.

Volvemos a transponer, y, ya tenemos lo que estábamos buscando.

Usamos la primera fila como encabezado, y, hemos conseguido lo que queríamos.

Pero, ahora, tenemos el problema de que tenemos varias columnas, que son los años junto con el trimestre, que hacen referencia a lo mismo, y, eso no puede ser a la hora de tener un modelo de datos.

Entonces, voy a seleccionar la primera columna, hacemos clic con el botón alternativo del ratón, y, elegimos anulación de dinamización de otras columnas.

Con esto, tenemos una sola fila, con todos los años.

Ahora, vamos a separar el año del trimestre, para ello, seleccionamos la columna de los años, vamos a la pestaña transformar,

En el desplegable que se abre, elegimos por delimitador.

En la ventana, seleccione o escriba el delimitador, seleccionamos la coma.

Dejamos marcado, en cada aparición del delimitador, que aparece por defecto.

Aceptamos, y, tenemos separado el año del trimestre.

Ahora, vamos a cambiar el nombre, es decir, en vez de que aparezca Q, que aparezca la palabra Trimestre.

Primero, le vamos a cambiar el nombre a los encabezados.

Ahora, seleccionamos la columna de trimestres, hacemos clic con botón alternativo del ratón sobre ella, y, elegimos reemplazar valores.

Se abre la ventana de reemplazar valores, en la ventana, valor que buscar, ponemos la letra Q, y, en la ventana, reemplazar con, ponemos la palabra, Trimestre, con un espacio al final, y, aceptamos, y, ya lo tenemos.

Podemos decir que nuestro modelo esta normalizado.

Mover columnas

Vamos a ver las distintas formas que tenemos de mover columnas, cargamos un modelo cualquiera de los que hemos estado usando.

Podemos mover una columna mediante la opción Mover, esta opción se encuentra en el grupo cualquier columna, en la pestaña transformar.

En la opción Mover , las opciones disponibles son:

· Antes

· Después

· Al principio

· Hasta el final

También la podemos encontrar esta opción al hacer clic con el botón secundario en una columna, y, desplegando mover.

Para desplazarnos una columna a la izquierda, por ejemplo, la columna distribuidor, hacemos clic con botón alternativo de ratón sobre la columna, desplegamos mover, y, seleccionamos izquierda.

La nueva ubicación de la columna es ahora una columna a la izquierda de su ubicación original.

Si lo que queremos es desplazarnos una columna a la derecha, seleccionamos derecha. si queremos que la columna se mueva al espacio más a la izquierda del conjunto de datos, seleccionamos al principio, si desea que la columna se mueva al espacio más a la derecha del conjunto de datos, seleccione hasta al final.

Otra manera de desplazar una columna a través del conjunto de datos es arrastrar y colocar la columna, solo debemos de mover la columna al lugar donde deseamos colocarla.

Si deseamos buscar una columna específica, vamos a la pestaña vista, dentro del grupo columnas, hacemos clic en ir a columna.

Se abre la ventana, ir a columna, donde podemos ver los encabezados de columnas de nuestro modelo, por ejemplo, voy a seleccionar la columna de fecha en el modelo, y, en la ventana ir a columna, seleccionamos la columna de pais.

Aceptamos, y, vemos que nos hemos movido a la columna de pais.

Ahora, vamos a ver la l opción group by, o, agrupar por.

Vamos a cargar el modelo tabla ventas.

Queremos saber lo que cada cliente ha gastado.

Los clientes se repiten, porque han hecho varias compras, lo que queremos, es que me aparezca un cliente único con el total de factura.

Lo primero que vamos a hacer es referenciar la tabla ventas, para ello, hacemos clic con el botón alternativo del ratón sobre la tabla ventas, y, seleccionamos referencia.

Le cambiamos el nombre a ventas por cliente, podemos ver las consultas, en el panel de consultas.

Nos vamos a la pestaña de inicio, dentro del grupo transformar, hacemos clic en group by, o, agrupar por.

Se abre la ventana, agrupar por, hacemos clic en uso avanzado, porque así podemos añadir más de una condición.

Desplegamos NroFactura, y, elegimos cliente.

Como queremos saber el total de ventas por cliente, en la ventana, nuevo nombre de columnas, ponemos, por ejemplo, Total Ventas, en la ventana operación, elegimos suma, y, desplegamos la ventana columna, y, elegirnos montofactura.

Hacemos clic en agregar agregación.

Porque vamos a elegir, en la ventana nuevo nombre de columna, ponemos, recuento, en la ventana, operación, no tenemos que poner nada, porque de forma predeterminada ya pone recuento de filas, y, en la ventana columna, tampoco, debemos de poner nada, aunque si quisiéramos, y, desplegamos la ventana columna, veremos que no aparece nada.

Clic en aceptar.

Podemos ver que solo nos aparece los clientes con el total de ventas, y, un recuento, es decir, las veces que aparece cada cliente en el listado.

Ahora, volvemos a referenciar la tabla ventas, ahora, lo que queremos, es ver lo que se ha vendido por año.

Me voy a quedar solo con las columnas que hacen falta, que es la fecha y el monto de factura, por lo que seleccionamos ambas columnas, hacemos clic con botón alternativo del ratón, y, elegimos quitar otras columnas.

Ahora, teniendo seleccionada la columna de fechas, vamos a la pestaña agregar columna, dentro del grupo de número, desplegamos fecha, nos movemos hasta año, y elegimos año.

Se crea una nueva columna solo con los años.

Quitamos la columna de fecha, porque no nos va a ser falta, movemos la columna año a la primera posición

Ahora, vamos a agrupar por, dentro de la pestaña de inicio.

Seleccionamos la columna año, en la ventana nuevo nombre de columna, ponemos total por año, en la ventana, operación, elegimos suma, y, en la ventana columna, seleccionamos la columna montofactura.

Es decir, queremos el total por año.

Clic en aceptar, y, podemos ver los totales por año.

Pero, ahora, queremos ver también el total por año y trimestre, por lo que vamos a la ventana pasos aplicados, vamos a retroceder hasta donde aún estaba la fecha, es decir, al paso de otras columnas quitadas.

Seleccionamos la columna fecha, y, vamos a la pestaña de agregar columna, desplegamos fecha, en el desplegable de trimestre, elegimos trimestre del año.

Nos saldrá una ventana de aviso, si queremos insertar el paso, hacemos clic en sí.

Nos crea una columna con el número de trimestre.

Nos colocamos en el último paso, y, hacemos clic en la rueda dentada.

Se abre la ventana, agrupar por, hacemos clic en uso avanzado.

Hacemos clic en agregar agrupación.

Seleccionamos trimestre, en la ventana que se abre.

En la ventana, nuevo nombre de columna, en la ventana operación, y, en la ventana columna, ya aparecen los valores anteriores.

Hacemos clic en aceptar, y, tenemos los totales por año y trimestre.

También, disponemos una opción que es, especificar datos, esto la utilidad que tiene es la crear nuestra propia tabla dentro de Power Query.

En la pestaña de inicio, dentro del grupo nueva consulta, tenemos especificar datos.

Primero, vamos a crear una consulta en blanco, para ello, en un libro en blanco, vamos a la pestaña datos, dentro del grupo obtener y transformar, desplegamos obtener datos, desplegamos de otras fuentes, en la ventana que se abre a la derecha, seleccionamos consulta en blanco.

Vemos el editor de Power Query, en el panel de consultas, vemos una consulta, llamada Consulta1, y, sin datos.

Ahora, vamos a hacer clic en especificar datos.

Se abre la siguiente ventana:

Se abre la ventana de crear tabla.

En la parte inferior, vemos una ventana llamada, nombre, con el nombre de la tabla, el cual podemos cambiar.

Hacemos doble clic sobre el encabezado, columna1, y, le ponemos como nombre, la palabra nombre.

Hacemos clic en el símbolo mas (+), para agregar una nueva columna.

A esta nueva columna, la llamamos apellido.

Hacemos clic en:

Escribimos el primer nombre.

Tabulamos, y, escribimos el apellido.

Solo tenemos que ir seleccionando las siguientes celdas, e, ir introduciendo datos.

Hacemos clic en aceptar.

Vemos que nos ha creado una tabla con los datos que hemos introducido.

Si queremos modificar o añadir nuevos datos, en la ventana, pasos aplicados, hacemos clic en la rueda dentada del paso origen.

Volvemos a acceder a la ventana de crear tabla, y, continuamos introduciendo datos.

Para eliminar un registro, lo seleccionamos con botón alternativo del ratón, y, elegimos eliminar.

Si queremos añadir nuevos datos, hacemos clic en el símbolo más después del último registro, o, clic en una celda.

Esta opción es poco usada, porque generalmente nos vamos a traer el modelo, pero podemos usarlo, por ejemplo, para crear una tabla maestra, es decir, una tabla de registros únicos.

Tipos de datos de Power Query

Power Query, es una herramienta potente y que ahorra tiempo, que se utiliza para recopilar y analizar grandes conjuntos de datos organizándolos de forma que su consumo en Excel sea mucho más fácil.

Con esta característica, podemos crear tipos de datos personalizados a partir de cualquier origen de datos, y, cargarlos en la cuadrícula de Excel como valores enriquecidos.

Antes de nada, vamos a ver que son los tipos de datos.

Podemos obtener datos geográficos y de cotizaciones en Excel.

Es tan fácil como escribir texto en una celda y convertirlo al tipo de datos Acciones o al tipo de datos Geography.

Estos dos tipos de datos, se consideran tipos de datos vinculados porque tienen una conexión a un origen de datos en línea.

Esa conexión nos permite recuperar información útil e interesante con la que puede trabajar y que puede actualizar.

Tenemos una tabla con distintos países.

Seleccionamos el rango de ciudades, vamos a la pestaña datos, dentro del grupo tipo de datos, hacemos clic en información.

Aparece un icono en la parte izquierda del nombre de la ciudad, y, si seleccionamos una celda, aparece otro icono en la parte derecha.

Vemos que en el icono de la derecha en la parte superior, aparece un signo más.

Si hacemos clic en dicho icono, se abre una ventana con las propiedades a que podemos acceder.

Si hacemos clic por ejemplo en población, nos aparecerá una nueva columna con la población de ese país.

Esto lo podemos hacer con cualquier propiedad disponible, también decir, que depende si ponemos una ciudad o un país, tendremos más o menos propiedades.

El icono indica que son datos geográficos.

Pues una vez visto una pequeña introducción de lo que son los tipos de datos, seguimos con tipos de datos en Power Query.

Vamos a abrir una hoja de cálculo en blanco, vamos a la pestaña datos, dentro de obtener y transformar, nos traemos un archivo Excel, por ejemplo, ventas celulares.

Seleccionamos todas las columnas.

En el Editor de Power Query, seleccione la columna pais y, a continuación, hacemos clic en el botón Crear tipo de datos, dentro del grupo columna estructurada, en la pestaña Transformar.

Se abre la ventana, creación de un tipo de datos, voy a dejar el nombre que pone de forma predetermina en la ventana, nombre del tipo de datos, y, en mostrar columna, elijo fecha, esto quiere decir que todas las columnas estarán contraídas en la columna fecha.