Cómo usar Power Query en Excel: la guía completa

Power Query es una poderosa herramienta de inteligencia comercial en Excel que le permite importar, limpiar y transformar datos según sus necesidades. Si alguna vez ha pasado horas limpiando datos (nosotros lo hemos hecho) o ha tenido problemas para fusionar datos de varias fuentes (también lo hemos hecho), esta función le facilitará la vida.

La mejor manera de aprender a usar Power Query en Excel es realizar algunas tareas comunes, como importar datos y usar el Editor de Power Query para transformar las tablas resultantes y combinar los datos.

Este artículo le muestra ejemplos claros de Power Query con guías paso a paso . Al dominar Power Query, puede elevar su trabajo al siguiente nivel.

¿Estás listo? Hagámoslo.

Tabla de contenido

• Cómo saber si puede acceder a Power Query
• Cómo importar datos con Power Query
• Cómo usar el editor de consultas de energía
  • Vista previa de datos
  • Propiedades
• Cómo trabajar con múltiples fuentes de datos
• Cómo agregar datos con Power Query
• Cómo fusionar datos con Power Query
• 5 formas de transformar datos con Power Query
  • 1. Expansión y condensación de datos
  • 2. Clasificación de datos
  • 3. Formateo de datos
  • 4. Agregar y quitar columnas
  • 5. Cambio de tipos de datos
• Trabajar con fórmulas y lenguaje M
  • Barra de formulas
  • Escribir y modificar código M
• ¿Qué es la sintaxis del lenguaje M?
• Cómo actualizar las conexiones de datos
• Cómo integrar Power Query con el modelo de datos
• Cómo automatizar con Power Query y VBA
• Funciones avanzadas de Power Query
• Colaboración y uso compartido
• Terminemos con esto

Cómo saber si puede acceder a Power Query

Antes de comenzar a usar Power Query, debe asegurarse de tener una versión de Excel que tenga la función.

Aquí hay una breve descripción general de Power Query por versión:

• Excel 2010 y Excel 2013: disponible como complemento que debe descargarse e instalarse.

• Excel 2016: integrado en Excel como “Get & Transform”.

• Excel 2019: rendimiento y funcionalidad mejorados.

• Excel 2021 y Excel para Microsoft 365: integración con fuentes de datos en la nube.

• Excel para Mac: solo disponible desde Microsoft 365 versión 16.69 y posteriores.

Para verificar que la herramienta esté integrada en su libro de Excel (2016 o posterior), vaya a la pestaña Datos y busque la sección "Obtener y transformar datos" en esta imagen:

Si tiene Excel 2010 o 2013 en Windows, puede descargar , instalar y habilitar el complemento Power Query.

Cómo importar datos con Power Query

La herramienta Power Query le permite importar y conectarse a una amplia gama de fuentes de datos:

• Archivos, incluidos los formatos CSV, XML y JSON

• URL web o puntos finales de API

• Bases de datos, incluidas Access, SQL Server, Oracle y la mayoría de las bases de datos modernas

• Servicios de Azure, archivos de SharePoint y fuentes de OData

La fuente de datos más fácil para comenzar es un archivo CSV simple con varias líneas de datos. Cree un archivo de texto y agregue estos cuatro nombres y apellidos, separados por una coma:

• Joe, blogs

• Ana, Ryan

• James, Estuardo

• María, marrón

Asegúrese de guardar el archivo con la extensión .CSV. Su archivo se verá así:

Para cargar los datos con Power Query, siga estos pasos:

1. Vaya a la pestaña Datos.

2. Haga clic en el botón "Obtener archivos" en la pestaña Obtener y transformar datos.

3. Seleccione su archivo de muestra.

Es posible que deba esperar unos segundos para que la herramienta extraiga los datos en el panel de vista previa. Power Query utiliza una gran cantidad de recursos de la máquina, incluso para una pequeña importación, así que tenga paciencia.

Sugerencia: cierre otras aplicaciones para acelerar el rendimiento.

Power Query mostrará la ventana "Importación de texto/CSV" con una vista previa de sus datos.

Si los datos parecen correctos, puede proceder a cargar o transformar los datos:

• el botón Cargar cargará datos directamente en Excel.

• el botón Transformar datos le permite modificar los datos antes de cargarlos.

¿Notó que el archivo de muestra no tenía un encabezado? Si se carga directamente en Excel, los encabezados serán "Columna 1" y "Columna 2".

En su lugar, puede extraer los datos en el editor de Power Query y realizar algunos cambios. Para hacerlo, haga clic en el botón "Transformar datos".

Ahora se abre Power Query Editor, lo que nos lleva a la siguiente sección.

Cómo usar el editor de consultas de energía

En esta sección, exploraremos algunos aspectos esenciales del trabajo con Power Query Editor.

Vista previa de datos

En el área de Vista previa de datos, puede ver e interactuar con sus datos importados. Puede filtrar filas, modificar tipos de datos de columnas y remodelar los datos para adaptarlos a sus necesidades.

Cuando haya terminado, Excel cargará los datos transformados en una hoja de trabajo.

Usando el archivo de muestra, puede editar los nombres de las columnas directamente en este panel.

Para hacerlo:

1. Haga clic derecho en el encabezado de la columna.

2. Haga clic en "Renombrar" en el menú desplegable.

3. Escriba los nombres preferidos en los cuadros de encabezado.

A medida que realiza cambios, la sección "Pasos aplicados" a la derecha del panel de vista previa se actualiza para reflejar cada transformación, lo que le permite realizar un seguimiento y modificar los pasos de procesamiento de datos según sea necesario.

Propiedades

Establecer propiedades para sus consultas es esencial para organizar y realizar un seguimiento de su trabajo.

El panel Configuración de consulta está a la derecha de la ventana de Power Query.

1. Haga clic en el enlace "Todas las propiedades".

2. Edite el nombre de la consulta para que sea descriptivo.

3. Agregue una descripción para proporcionar más información.

Es fácil olvidar para qué se configuraron las consultas de la semana pasada. En este caso, se ha cambiado el nombre de la consulta y se ha agregado una descripción que da el propósito de la consulta.

Si desea obtener más información sobre las opciones disponibles, consulte esta descripción general del Editor de Power Query .

Cómo trabajar con múltiples fuentes de datos

El editor de Power Query ofrece dos métodos principales para combinar datos de múltiples fuentes: agregar y fusionar.

Anexar mesas de unión verticalmente, apilándolas una encima de la otra. La combinación le permite unir tablas en función de columnas comunes.

Para ver cómo funciona esto, cree un segundo archivo CSV pequeño con la misma estructura que el primero. Esta vez, ponga dos nombres (nombre y apellido) en el archivo:

• Joe, blogs

• Laura, caña

Observe que uno de estos nombres se repite del archivo anterior.

Ahora siga estos pasos para extraer los datos en su libro de trabajo de Microsoft Excel.

1. Cierre la ventana del editor de Power Query y guarde los cambios.

2. Agregar una nueva hoja de trabajo.

3. Siga los pasos de la sección anterior para importar el segundo archivo.

4. Haga clic en el botón Transformar.

5. Cambie el nombre de los encabezados de columna como "Nombre" y "Apellido".

El panel derecho de la hoja de cálculo de Excel muestra las consultas y conexiones actuales. Ahora debería ver dos consultas en el panel.

Cómo agregar datos con Power Query

En esta sección, agregará el segundo archivo CSV de muestra al primero.

Siga estos pasos para agregar datos:

1. Haga clic con el botón derecho en la primera tabla (Nombres de clientes) en el panel Consultas y conexiones.

2. Elija "Editar" para abrir la tabla en el Editor de Power Query.

3. Haga clic en Agregar consultas en la sección Combinar.

4. Elija la segunda tabla de la lista desplegable de tablas para agregar.

El resultado serán dos filas adicionales en la primera tabla.

Observe que se repite una fila; así es como funciona la operación de agregar. Agrega los datos de una tabla a otra, independientemente de si los datos ya existen.

Puede eliminar los datos duplicados en Power Query Editor con estos pasos:

1. Haga clic en el encabezado de la columna "Nombre".

2. Mantenga presionada la tecla "Ctrl" (o la tecla "Cmd" en Mac).

3. Haga clic en los encabezados de ambas columnas.

4. Haga clic en "Eliminar filas" en la pestaña Inicio de la cinta de Power Query Editor.

5. Seleccione "Eliminar duplicados" en el menú desplegable.

Cómo fusionar datos con Power Query

Cuando combina datos, debe elegir las columnas coincidentes entre las consultas.

Suponga que tiene dos archivos delimitados por comas donde uno contiene números de teléfono de clientes y el otro contiene direcciones. Los dos archivos se ven así:

Siga las instrucciones de las secciones anteriores para cargar cada archivo en su propia consulta de energía. Luego puedes practicar fusionando las tablas.

En el caso de nuestros datos de muestra, ambas columnas (Nombre y Apellido) deben elegirse como columnas coincidentes. Mantenga presionada la tecla Ctrl para elegir varias columnas.

Sigue estos pasos:

1. Haga clic con el botón derecho en la primera tabla del panel Consultas y conexiones.

2. Elija "Editar" para abrir la tabla en el Editor de Power Query.

3. Seleccione "Combinar consultas" del grupo "Combinar".

4. Elija la segunda tabla en la lista desplegable de tablas.

5. Haga clic en la primera columna de la primera tabla.

6. Mantenga presionada la tecla Ctrl y haga clic en la segunda columna de la primera tabla.

7. Mantenga presionada la tecla Ctrl y haga clic en ambas columnas en la segunda tabla.

8. Mantenga el operador de unión predeterminado como "Izquierda exterior".

9. Haga clic en "Aceptar" para aplicar los cambios.

El operador de unión "Izquierda exterior" garantiza que se eliminen los datos duplicados.

En Power Query Editor, verá una nueva columna llamada "Tabla" o "Tabla anidada" que contiene los datos combinados de la segunda consulta.

1. Haga clic en el icono de expansión (dos flechas) en el encabezado de esta columna.

2. Desmarque la casilla "usar columna original como prefijo" (esto evita nombres largos de columna).

3. Haga clic en Aceptar".

4. Haga clic en "Cerrar y cargar".

La tabla combinada tenía cuatro columnas, con los detalles de teléfono y dirección combinados. Es posible que todavía tengas que arreglar un poco.

Por ejemplo, es posible que desee editar el nombre de la tabla (Merge1) y la columna de dirección en la imagen a continuación.

5 formas de transformar datos con Power Query

La transformación de datos en su archivo de Excel se simplifica con Power Query, lo que le permite limpiar, organizar y manipular sus datos de manera efectiva.

Algunas de las formas más comunes de transformar datos son:

• Expansión y condensación de datos

• Clasificación de datos

• Datos de formato

• Adición y eliminación de columnas

• Cambio de tipos de datos

1. Expansión y condensación de datos

La expansión y condensación de datos es importante para que sus conjuntos de datos sean más manejables. Power Query le permite expandir o contraer tablas para una vista más organizada.

La sección anterior le muestra cómo ampliar incluyendo datos de tablas relacionadas.

También puede condensarlo para incluir solo datos resumidos en función de una agregación de su elección, como suma, conteo o porcentaje.

2. Clasificación de datos

Para ordenar los datos, puede cambiar rápidamente el orden por valores ascendentes o descendentes.

Haga clic en la flecha hacia abajo junto al nombre de una columna y elija "Ordenar de forma ascendente" o "Ordenar de forma descendente".

3. Formateo de datos

La pestaña Transformar en el Editor de Power Query ofrece muchas opciones.

Por ejemplo, el menú desplegable Formato le permite cambiar todos los datos en una columna o la tabla completa a mayúsculas o minúsculas.

4. Agregar y quitar columnas

Power Query simplifica el proceso de agregar, eliminar y modificar columnas.

To remove a column, simply right-click on the header of the undesired column and select “Remove.” This will permanently delete the column and all of its data from the table.

To add a new column, select the existing column where you’d like the new one to appear. Then use the “Add Column From Examples” feature to create a custom column or to perform calculations between two or more columns.

5. Changing Data Types

Working with consistent and accurate data types is crucial for accurate analysis. Power Query allows you to change data types easily:

1. Right-click on the column header.

2. Choose “Change Type”.

3. Select the desired data type from the list provided.

Changing data types can be particularly useful when dealing with percentage data.

Por ejemplo, es posible que necesite convertir puntos decimales en porcentajes o convertir porcentajes basados ​​en texto en numéricos para facilitar el cálculo y la comparación.

Trabajar con fórmulas y lenguaje M

Al usar Power Query en Excel, encontrará el lenguaje M, que es el corazón de la creación de fórmulas avanzadas, la manipulación de datos y la producción de consultas eficaces. En esta sección, aprenderá sobre:

• la barra de fórmulas

• codigo m

• los elementos esenciales de la sintaxis del lenguaje M

Barra de formulas

La barra de fórmulas en el editor Power Query de Excel es el área donde ingresará sus fórmulas y código M.

Para usarlo, vaya al editor de Power Query seleccionando una consulta del panel Consultas y conexiones. La barra de fórmulas se encuentra sobre el panel de vista previa de datos.

Escribir y modificar código M

Para crear consultas versátiles y potentes en Excel, deberá escribir y modificar el lenguaje Power Query conocido como código M. Aquí hay algunos consejos sobre cómo empezar:

• Utilice el panel Pasos aplicados para escribir código paso a paso, lo que facilita el seguimiento y la modificación de la lógica de su consulta.

• Combine el código M con una tabla de Excel, tablas dinámicas y fuentes de datos externas, como Azure y archivos de texto, para crear informes dinámicos y automatizados.

• Ajuste las opciones de consulta para refinar su preferencia de carga de datos y mejorar el rendimiento.

A medida que se familiarice con el código M, encontrará que puede automatizar y optimizar muchos procesos, haciendo que su análisis de datos en Excel sea más eficiente y poderoso.

Para ver el código detrás de una consulta, siga estos pasos:

1. Abra la consulta en el Editor de Power Query.

2. Haga clic en "Editor avanzado" en la pestaña Inicio.

Este es el código detrás de nuestra tabla fusionada de muestra (la suya puede verse diferente):

¿Qué es la sintaxis del lenguaje M?

Comprender la sintaxis del lenguaje M es crucial para crear fórmulas efectivas de Power Query. Tenga en cuenta los siguientes puntos:

• M Language distingue entre mayúsculas y minúsculas, así que tenga cuidado con las mayúsculas.

• Use corchetes [] para hacer referencia a columnas, registros y listas.

• Para hacer referencia a un paso anterior en su consulta, utilice su nombre de variable (p. ej., #”Nombre del paso”).

• Genera un paso de fórmula de consulta con la instrucción "in".

A medida que adquiera experiencia con el flujo de lenguaje de Power Query , descubrirá nuevas formas de transformar y analizar sus datos, lo que hará que sus libros de Excel sean un activo aún más valioso.

Mire el video a continuación, es hora de sacar el cepillo de fregar y limpiar sus datos desordenados.

Cómo actualizar las conexiones de datos

Una de las características clave de Power Query es la capacidad de actualizar fácilmente su conexión de Power Query.

Cuando sus fuentes de datos se hayan actualizado y necesite la información más reciente en su libro de trabajo, puede actualizar las conexiones de datos en el panel Consultas y conexiones.

Si no ve el panel a la derecha de su hoja de trabajo, puede abrirlo yendo a la tabla de Datos y haciendo clic en "Consultas y Conexiones".

Cuando el panel Consultas y conexiones está abierto, enumera todas las consultas. Sigue estos pasos:

1. Haga clic derecho en la consulta que desea actualizar.

2. Seleccione Actualizar en el menú contextual.

Esto actualizará su libro de trabajo con los datos más recientes de sus fuentes de datos conectadas.

Cómo integrar Power Query con el modelo de datos

El modelo de datos de Excel es una característica poderosa que le permite crear herramientas de inteligencia comercial, como tablas dinámicas, basadas en relaciones de datos.

El modelo de datos es básicamente una base de datos oculta que contiene los datos importados y las relaciones entre las tablas.

Es parte de la función Power Pivot que está integrada en las versiones actuales de Excel. Es posible que deba instalar Power Pivot como un complemento para acceder a él en versiones anteriores).

Puede integrar sus conexiones de datos de Power Query con el modelo de datos con estos pasos:

1. Cargue sus datos usando Power Query como se describe en las secciones anteriores.

2. En lugar de cargarlo directamente en el libro de trabajo, seleccione Cargar en en el menú Cargar.

3. Seleccione la casilla Agregar estos datos al modelo de datos en el cuadro de diálogo Importar datos.

Cómo automatizar con Power Query y VBA

Con la integración de VBA (Visual Basic para aplicaciones), puede crear conexiones de datos personalizadas y automatizar los pasos de transformación de datos en Power Query.

Puede aprovechar sus habilidades existentes de VBA para crear modelos de datos avanzados mediante la incorporación de datos de plataformas como Microsoft Access, SharePoint y Analysis Services.

Aunque Power Query no tiene soporte nativo para VBA, puede usar código VBA para abrir, actualizar y manipular consultas de Power Query indirectamente.

Esto le permite crear flujos de trabajo de datos potentes y automatizados y agilizar su proceso de análisis de datos.

Aquí hay un código de muestra que actualiza una consulta de energía en el libro de trabajo actual.

:

Dim wb como libro de trabajo, pq como objeto

Establecer wb = Este libro de trabajo

Establecer pq = wb.Consultas ("Nombres de clientes")

 pq.Actualizar

Aquí está el código dentro de una macro de Excel VBA:

Funciones avanzadas de Power Query

Power Query de Excel ofrece una gran cantidad de funciones avanzadas para simplificar y mejorar su proceso de análisis de datos. Al aprender a usar estas funciones de manera efectiva, puede llevar sus habilidades de análisis de datos a un nivel completamente nuevo.

Las herramientas integradas de análisis de datos incluyen:

• Columnas condicionales para aplicar lógica compleja a sus transformaciones de datos.

• Funciones de agrupación y agregación para resumir sus datos de manera significativa.

• Operaciones Pivot y Unpivot para remodelar sus datos para un análisis más fácil.

Encontrará muchas de estas características en la pestaña Transformar en el Editor de Power Query.

Además, puede conectar Power Query a herramientas de Business Intelligence como LuckyTemplates para obtener opciones de visualización y análisis de datos más avanzadas .

Colaboración y uso compartido

Compartir sus conexiones de datos, consultas y transformaciones con los miembros de su equipo es esencial para una colaboración eficaz.

Puede compartir un libro de trabajo con consultas guardándolo en una unidad de red, SharePoint o alguna otra ubicación de almacenamiento compartido.

También puede exportar el código M detrás de una sola consulta con estos pasos:

1. Edite la consulta en el Editor de Power Query.

2. Haga clic en "Editor avanzado" en la pestaña Inicio.

3. Copie todo el código M en la ventana del Editor avanzado.

4. Abra un editor de texto (por ejemplo, el Bloc de notas) y pegue el código M copiado en el editor de texto.

5. Guarde el archivo de texto y envíelo a su colega.

Terminemos con esto

Con una variedad de funciones y su interfaz fácil de usar, Power Query es una herramienta esencial para los usuarios de Excel que buscan trabajar de manera efectiva con datos externos.

Esta valiosa función ayuda a agilizar el proceso de creación de informes, limpieza de datos y realización de análisis complejos en sus tablas de datos. Usando

En última instancia, Power Query le ahorra tiempo y aumenta la eficiencia cuando trabaja con archivos de Excel.