Power Query: conexión de datos desde Excel

El grupo de botones Obtener y Transformar se encuentra en la pestaña Datos de la cinta de opciones de Excel. Este grupo es fundamental para el uso de Power Query, ya que ofrece diversas herramientas que permiten la importación y la transformación de datos de múltiples fuentes.

Versiones de Power Query

Según la versión de Excel con la que trabajemos, el aspecto y la oferta de las fichas de la cinta de opciones y de estos grupos de botones puede diferir, y obviamente a medida que aparecen nuevas versiones, encontraremos más opciones de obtención y transformación (de conexión con orígenes de datos diversos).

Por ejemplo, para Excel 365 tiene la forma siguiente (cuando la configuración de pantalla está de forma que los botones aparecen comprimidos:

Y si la configuración de la pantalla hace que los botones aparezcan en grande, la forma de la ficha Datos es esta:

En Excel 2021 (por ejemplo), sin embargo, el aspecto es este:

Obtener y transformar

Ejemplo de tabla exportada desde programa ERP a formato Excel, susceptible de ser depurada y transformada

Al hacer clic en el botón 'Obtener datos', se despliega un menú que presenta diversas categorías de origen de datos, incluyendo como más fundamentales:

• De un archivo: Archivos como Excel, CSV, XML, archivos de texto o muy importante, carpetas en las que podemos tener o bien archivos en formato Excel, o bien archivos en formato .CSV. Si conexionamos con una carpeta, estaremos conexionando con todos los archivos de un mismo formato que contenga esa carpeta. De este modo, cada mes, cada periodo concreto de nuestro trabajo, simplemente moveremos nuevos archivos de datos correspondientes a ese nuevo periodo a esa carpeta, y solo con Actualizar, esos nuevos datos se integrarán en el origen de datos, actualizándose en consecuencia cualquier cuadro de mando creado a partir del resultado de esta consulta de Power Query.
• Bases de datos como Access, SQL Server, MySQL, Oracle.
• Servicios en línea como SharePoint y Salesforce, entre otros.
• Otros formatos... Podemos conexionar con tablas que se encuentren dentro de archivos .PDF, incluso datos de una tabla fotografiada y contenida en un archivo de imagen .JPG.

Esto permite a los usuarios acceder a una amplia gama de fuentes de datos para, si es necesario transformarlos, y finalmente comenzar el proceso de análisis.

Un ejemplo práctico es, cuando un analista de datos necesita importar información de ventas desde un archivo CSV.

Al seleccionar 'Obtener datos' > 'De archivo' > 'De texto/CSV', se abre un cuadro de diálogo para seleccionar el archivo fuente. Una vez cargado, Excel muestra una vista previa de los datos y ofrece tres opciones fundamentalmente:

• Transformar (transformar datos), o lo que es lo mismo Editar los datos para "pasarlos" por Power Query en el caso de que no se encuentran bien estructurados y/o limpiados (eliminado campos que no nos interesan, filtrado ciertas filas, filas en blanco...). En este caso se crea una consulta en la que se aplican unos pasos sobre los datos originales transformándolos para dejarlos, digamos bien...
• O bien Cargar directamente los datos a Excel sin transformar, es decir, sin pasarlos por transformaciones y/o acciones Power Query. En este caso se crea también una consulta, en la que solo tendremos un paso, la conexión con el origen de los datos, pero ninguna acción realizada sobre los datos.
• O bien Cargar en. Si elegimos esta opción, se da paso a un panel que ofrece la opción de cargar los datos como tabla en la hoja que se especifique, generar directamente una tabla dinámica, un gráfico dinámico basado en esos datos o bien crear la conexón únicamente. La casilla final de Agregar estos datos al modelo de datos, lleva la tabla a Power Pivot, entorno en el que podríamos relacionar esta tabla con otras tablas, definir medidas, columnas calculadas, en suma definir un modelo de datos:

Otra herramienta importante dentro del botón 'Obtener datos' es 'Iniciar Editor de Power Query':

Esta opción, nos superpone en pantalla una nueva ventana del editor de Power Query sin tener un origen de datos concreto. Esto no es problema ya que desde dentro de Power Query, se puede elegir también el tipo de origen de datos desde el grupo de botones derecho de Nueva consulta.

Casos

Al conexionar con datos desde el entorno Excel, con el fin por ejemplo de diseñar cuadros de mando para analizar nuestras informaciones, se pueden presentar varios casos:

Si los datos a conexionar no requieren transformaciones y no se van a relacionar con otras tablas...

Querremos llevarlos a Excel en forma de tabla, a una hoja existente o bien una hoja nueva y además no agregaremos los datos al modelo de datos de Power Pivot.

En este caso elegiremos Cargar en... Y configuraremos el panel de la siguiente forma:

Si la tabla conexionada tiene menos de un millón de filas, no será preciso Agregar estos datos al Modelo de datos y si tiene por encima del millón de filas (concretamente 1.048.572 filas), entonces macaremos esta casilla para llevar los datos de la tabla a Power Query que es el entorno "infinito" de Excel a partir del cual podremos crear tablas dinámicas incluso a partir de datos de más de una tabla, eso sí, las tablas deberán estar relacionadas (esto se puede hacer en el modelo de datos de Power Pivot desde la vista Diagrama.

Dependiendo de la versión de Excel con la que trabajemos, el botón Obtener datos permite la conexión pasando, tras seleccionar el origen de datos, al panel en el que decidiremos si Cargar, Cargar en, o bien Transformar.

En otras versiones de Excel, el botón Obtener datos externos no da la opción de transformar datos, y es el botón de Nueva consulta el que presenta el panel que sí que ofrece estas mismas tres opciones de Cargar, Cargar en, o bien Transformar.

Es por este motivo por el que no hay que aprenderse las cosas de memoria, sino razonar las posibilidades y opciones en virtud de lo que necesitemos en cada momento.

Establecer la conexión con los datos sin dejarlos en Excel en forma de tabla, para posteriores operaciones.

Nos puede interesar implicar una tabla en un proceso, por ejemplo, relacionándola o anexando sus datos a otra tabla, sin necesidad te cargar esos datos en forma de tabla en una hoja Excel. Ya veremos en qué casos esta acción nos puede resultar interesante. Se crea en este caso "una pieza u objeto", llamada consulta, en este caso sin ninguna acción de transformación realizada a partir de los datos conexionados. En este caso elegiremos Cargar.

Podríamos realizar lo mismo eligiendo Cargar en, y configurar adecuadamente el panel presentado.

En el panel deberemos elegir crear únicamente la conexión y el marcar o no la casilla inferior de Agregar estos datos al Modelo de datos, será en función de si deseamos llevar la tabla a Power Query para posteriores relaciones o definición de medidas y/o columnas calculadas en lenguaje DAX, o no.

Transformar los datos utilizando el Editor de Power Query.

Esto es especialmente útil para limpiar los datos, como eliminar duplicados, cambiar tipos de datos, filtrar filas, entre otras transformaciones. Por ejemplo, si un conjunto de datos contiene fechas en formatos inconsistentes, se pueden estandarizar utilizando las herramientas (botones u opciones de menús) de transformación en el editor de consultas llamado Power Query.

Además, Power Query ofrece Combinar consultas, para cuando queremos "juntar" columnas de tablas diferentes uniendo así datos de diferentes fuentes.

Power Query también permite Anexar filas de distintas tablas con la misma estructura.

Digamos, por ejemplo, que un gerente necesita comparar el rendimiento de ventas de diferentes regiones pero los datos están dispersos en varios archivos. Power Query permite combinar estos datos, anexándolos (sus filas) en una sola tabla.

Para alguna de estas tres grandes acciones, transformar, combinar o anexar datos, elegiremos el botón Transformar datos.

Cualquier otra combinación de opciones y casillas realizadas desde la ventana de Importar datos traerá consigo diferentes comportamientos para con los datos de la tabla conexionada, es decir de la tabla importada.

En resumen, el grupo de botones Obtener y Transformar de Power Query facilita la importación, manipulación, combinación y limpieza de grandes volúmenes de datos desde diversas fuentes, simplificando significativamente los procesos analíticos en Excel para una variedad de usuarios y aplicaciones profesionales.

Esta píldora formativa está extraída del Curso online de Power Query con Excel: transformación, combinación y limpieza de datos.