Parte 1. Power Query.

Jaime Franco Jimenez
15 may 2022
7 Min. de lectura

Actualizado: 27 may 2022

Incluye:

· ¿Qué es Power Query?

· Versiones de Power Query - 2010, 2013, 2016, y, 2019.

· Versión de Windows instalada. 32 o 64 bits.

· Pasos de Power Query.

· Que es ETL - Enabled-Transform-Loading.

· Objetivo de Power Query.

· Cinta de opciones.

· El panel central.

· Panel de configuración de consulta.

· Editor avanzado.

· Filtro automático.

· Barra de búsqueda.

· Anulación de dinamización de columnas.

· Anulación de dinamización de otras columnas.

· Detectar tipo de datos.

Microsoft Power Query para Excel es un complemento que nos permite la detección y transformación de datos.

Power Query, en la versión 2016, viene incorporado, dentro de la pestaña Datos, como obtener y transformar.

Power Query es una tecnología de conexión de datos que permite detectar, conectar, combinar y refinar orígenes de datos para satisfacer nuestras necesidades de análisis.

Las características de Power Query están disponibles en Excel y Power BI Desktop.

Con Power Query podemos conectarnos a muchos orígenes de datos diferentes para poder trabajar con los datos que necesitamos, y, darles forma a esos datos, como, por ejemplo, quitar una columna, cambiar un tipo de datos o combinar tablas.

Una vez que hayamos dado forma a nuestros datos, podemos volver a cargar los datos en Excel.

Los pasos de Power Query suelen producirse de la siguiente manera:

· Conectarse: creamos una conexión a datos en la nube, en un servicio o localmente.

· Transformar: después, damos forma a los datos según nuestras necesidades; el origen original permanece sin modificar.

· Combinar: creamos un modelo de datos desde varios orígenes de datos y conseguimos una vista única de los datos.

· Compartir: una vez finalizada la consulta, podemos guardarla, compartirla o utilizarla para realizar informes.

Power Query se conoce como ETL, que en ingles significa Enabled-Transform-Loading, es decir, cargar, transformar y volver a cargar.

Con Power Query tenemos una gran cantidad de conectividad de una amplia gama de orígenes de datos, incluidos los datos de todos los tamaños y formas.

El objetivo de Power Query es ayudarnos a aplicar las transformaciones que necesitemos simplemente interactuando con un conjunto de cintas, menús, botones y otros componentes interactivos sencillos.

Power Query está disponible en Power BI Desktop a través de Power Query editor.

La cinta de opciones de consulta

La cinta de opciones del editor de Power Query consta de cuatro pestañas:

Inicio.
Transformar.
Agregar columna.
Vista.

La pestaña Inicio contiene las tareas de consulta más comunes, incluido el primer paso en cualquier consulta, que es obtener datos, que la encontramos dentro del grupo nueva consulta, en nuevo origen.

La pestaña transformar para tener acceso a tareas comunes de transformación de datos, como agregar o quitar columnas, cambiar tipos de datos, dividir columnas y otras operaciones controladas por datos.

La pestaña Agregar columna podemos usarla para realizar tareas adicionales asociadas a agregar una columna, como aplicar formato a los datos de la columna y Agregar columnas personalizadas.

En la pestaña Ver podemos activar o desactivar la presentación de determinados paneles o ventanas, y para mostrar el editor avanzado.

El panel consultas

El panel consultas muestra el número de consultas activas además del nombre de la consulta. Cuando selecciona una consulta en el panel izquierdo, los datos se muestran en el panel central, donde se les puede dar forma y transformar según nos sea necesario.

Vista de datos en el panel central

En el panel central (datos), se muestran los datos de la consulta seleccionada. Aquí es donde se realiza gran parte del trabajo en Power Query.

Panel Configuración de consulta

El panel Configuración de consulta es donde se muestran todos los pasos asociados con una consulta.

A medida que vamos aplicando pasos adicionales a la consulta, estos se capturan en la sección pasos aplicados.

Podemos acceder a uno de los pasos, simplemente, haciendo clic en el paso, podemos renombrarlo, o, incluso borrarlo.

Editor avanzado

Si deseamos ver el código que se está creando el editor de Power Query con cada paso, o si deseamos crear nuestro propio código, podemos usar el editor avanzado.

Para abrir el editor avanzado, seleccionamos la pestaña Ver en la cinta de opciones y, a continuación, seleccione editor avanzado. Aparecerá una ventana que muestra el código de la consulta actual.

Una vez hemos visto esta pequeña introducción, vamos a empezar a ver ejemplos, para conocer bien este complemento.

Para conectarnos a un archivo de Excel, Power Query requiere que usemos la ruta de acceso del archivo para buscar el archivo al que deseamos conectarnos, es lo que se denomina ruta de acceso de origen de datos.

Siempre es recomendable filtrar los datos en las primeras fases de la consulta o lo antes posible.

También es un procedimiento recomendado filtrar los datos que no sean relevantes para nuestro caso.

Esto nos va a permitir centrarnos mejor en la tarea de mostrar solo los datos relevantes en la sección de vista previa de los datos.

Podemos usar el menú filtro automático, que muestra una lista distinta de los valores que se encuentran en la columna para seleccionar los valores que desea conservar o filtrar.

También puede usar la barra de búsqueda para ayudarle a encontrar los valores de la columna.

Antes de ver un ejemplo, debemos de tener en cuenta que un modelo de datos no puede estar formado por diferentes columnas que hagan referencia a lo mismo.

Por ejemplo, no podemos tener más de una columna que haga referencia a años, solo debemos tener una columna de años, donde aparezcan todos los años, de esta manera, podremos trabajar con los años, por ejemplo, podemos totalizar por años, si están en columnas no podemos hacerlo.

Pues, el primer ejemplo que vamos a ver es justamente eso, tenemos un modelo de datos, donde tenemos los años en columnas, y, debemos de ponerlos en filas, hacer esto en Excel, aparte de ser mucho trabajo, sinceramente no sé si se puede hacer, creo que no.

Esto se conoce como normalizar datos.

Vamos a cargar dicho archivo a power Query, para ello, abrimos una hoja de cálculo en blanco.

Vamos a la pestaña datos.

Dentro de obtener y transformar, desplegamos obtener datos.

Elegimos desde un archivo.

En la ventana de la derecha elegimos desde un libro de Excel.

Se abre la ventana importar datos.

Buscamos nuestro archivo, lo seleccionamos, y, hacemos clic en importar.

Se abre la ventana del navegador, donde podemos ver nuestras hojas.

Seleccionamos nuestra hoja, que es Data, aparece a la derecha la vista previa de los datos que pertenece a la consulta que hemos seleccionado.

Hacemos clic en transformar.

Se abre el editor de power Query.

En el panel consultas, podemos ver el número de consultas activas, en este caso, solo hay una, además, del nombre de la consulta.

Cuando seleccionamos una consulta en el panel izquierdo, los datos se muestran en el panel central, donde se les puede dar forma y transformar según sea necesario.

El panel Configuración de consulta, o, pasos aplicados, es donde se muestran todos los pasos asociados con una consulta.

En pasos aplicados , podemos eliminar pasos y cambiar el nombre o el orden de estos como consideraremos.

Para ello, hacemos clic con el botón derecho para seleccionar el paso en la sección pasos aplicados y elegimos la acción que deseamos.

Todos los pasos aplicados se llevan a cabo en el orden en que aparecen.

Pues, una vez hecha estas aclaraciones, seguimos con el ejemplo, estamos viendo el modelo.

Como hemos dicho, no podemos tener varias columnas que hagan referencia a lo mismo, como es el caso.

En este caso, tenemos varias columnas que hacen referencia a lo mismo, al decir, hacer referencia a lo mismo, no quiero decir, que tengan el mismo nombre, sino, que hacen referencia a lo mismo.

Antes de hacer nada, debemos de asegurarnos que el tipo de datos de cada columna es correcto, para ello, miramos cada uno de los encabezados para verificarlo, en este caso, no esta reconociendo el tipo de datos, cuando aparece ABC/123, quiere decir, que no lo ha reconocido.

Para que lo reconozca, de forma rápida, vamos a la pestaña transformar, dentro del grupo cualquier columna hacemos clic en detectar tipo de datos.

Si nos fijamos en pasos aplicados, vemos como se ha añadido el paso, en este caso, se llama tipo cambiado.

Si nos fijamos en el modelo, podemos ver que ha cambiado el tipo de datos a las tres primeras columnas, a las columnas que hacen referencia a los años, no lo ha hecho, eso es porque no sabe distinguir si es texto, o, número.

Nos vemos obligado, a ponerlo nosotros de forma manual, para ello, hacemos clic del ratón sobre ABC/123, por ejemplo, del año 2010, se abre una ventana con los tipos de datos disponibles.

Vamos a elegir número entero.

Vemos, como ahora, aparece 123, quiere decir que es formato de número.

Lo mismo tenemos que hacer con el resto de las columnas de los años.

Ahora, seguimos, para solventar este problema, lo podemos hacer de dos maneras:

Una opción es anular dinamización de las columnas seleccionadas únicamente, a veces llamado aplanar los datos, para ponerlos en un formato de matriz para que todos los valores similares, como en este caso, los años, pues seleccionamos la primera columna, Año 2010, dejamos la tecla SHIFT o MAYUS pulsada, y, hacemos clic en la ultima columna, Año 2014, con esto quedan seleccionadas todas las columnas de los años.

Hacemos clic con botón alternativo de ratón, y, elegimos anulación de dinamización de columnas.

Podremos ver como ahora los años están en una sola columna, que es como debe de estar estructurado un modelo de datos.

Podemos ver, ahora, que Afganistán, que es el primer pais, aparece cinco veces, una vez para cada año, así, para el resto de los países.

Hay que tener en cuenta, que cuando tenemos el modelo como al principio, es decir, los años en columnas, si queremos crear una tabla dinámica, para resumir, por ejemplo, por años, no podríamos, porque cada año aparecería como un campo, por lo que no podríamos trabajar de forma conjunto con los años, de esta manera, lo podemos hacer sin mas problemas.

Otra opción es anulación de dinamización de otras columnas, esto quiere decir, seleccionamos una columna, y, va a anular las demás columnas menos esa.

Las siguientes columnas, no las voy a querer, me voy a quedar con la columna de pais.

Por lo que voy a seleccionar las tres columnas, hago clic con el botón alternativo del ratón, y, elijo quitar columnas.

Lo siguiente va a ser cambiar el nombre de los encabezados, para ello, hacemos doble clic sobre un encabezado, y, va a quedar de la siguiente manera:

Una vez que ya tenemos los datos transformados, podemos llevárnoslo a Excel para trabajar con ellos.

Para ello, dentro de la pestaña de inicio, hacemos clic en cerrar y cargar.

Vemos que se ha cargado en Excel, en formato de tabla.

En la parte derecha, vemos la ventana de consultas y conexiones, con nuestras consultas, donde vemos, que se han cargado 1240 filas, aunque cinco filas con errores.

Ahora, por ejemplo, voy a crear una tabla dinámica donde quiero ver las ventas por año, para ello, voy a la pestaña insertar, teniendo una celda dentro del rango de datos, hacemos clic en tabla dinámica.