Tutorial de excel power‌ consulta (obtener y transformar)

Si eres de esas personas que trabajan mucho con datos, puedes ser cualquiera ( contador , RR.HH., analista de datos, etc.), PowerQuery puede ser tu poderosa herramienta.

Permítanme ir al grano, Power Query es una de las habilidades avanzadas de Excel que necesita aprender y en este tutorial explorará PowerQuery en detalle y aprenderá cómo transformar datos con él.

Empecemos.

¿Qué es la consulta de energía de Excel?

Power Query es un complemento de Excel que puede usar para ETL. Esto significa que puede extraer datos de diferentes fuentes, transformarlos y luego cargarlos en la hoja de cálculo. Se puede decir que POWER QUERY es una máquina de limpieza de datos porque tiene todas las opciones para transformar datos. Es en tiempo real y registra cada paso que das.

¿Por qué debería utilizar Power Query (beneficios)?

Si tienes esta pregunta en mente, aquí tienes mi respuesta:

• Diferentes fuentes de datos : puede cargar datos en un potente editor de consultas desde diferentes fuentes de datos, como CSV , TXT, JSON, etc.
• Transforme datos fácilmente: normalmente, utiliza fórmulas y tablas dinámicas para las transformaciones de datos, pero con POWER QUERY puede hacer mucho con solo unos pocos clics.
• Es en tiempo real: escriba una consulta una vez y podrá actualizarla cada vez que haya un cambio en los datos, y transformará los nuevos datos que actualizó.

Permítanme compartir un ejemplo:

Imagina que tienes 100 archivos de Excel que contienen datos de 100 ciudades y ahora tu jefe quiere que crees un informe con todos los datos de esos 100 archivos. BIEN, si decide abrir cada archivo manualmente y copiar y pegar los datos de estos archivos, necesita al menos una hora para ello.

Pero con Power Query, puedes hacerlo en minutos. ¿Te sientes emocionado? BIEN.

Más adelante en este tutorial aprenderá cómo usar Power Query con muchos ejemplos, pero primero debe comprender su concepto.

El concepto de demanda de energía.

Para aprender Power Query, es necesario comprender su concepto, que funciona en 3 pasos:

1. Obtener datos

Power Query le permite obtener datos de diferentes fuentes como web, CSV, archivos de texto, varios libros de una carpeta y muchas otras fuentes donde podemos almacenar datos.

2. Transformar los datos

Después de ingresar datos en la consulta de energía, tiene un montón de opciones que puede usar para transformarlos y limpiarlos. Crea consultas para todos los pasos que realiza (en una secuencia, un paso tras otro).

3. Cargar datos

Desde el Editor de consultas avanzado, puede cargar los datos transformados en la hoja de trabajo, o puede crear directamente una tabla dinámica o un gráfico dinámico o crear una conexión de solo datos.

¿Dónde está Power Query (Cómo instalar)?

A continuación puede ver cómo instalar Power Query Access en diferentes versiones de Microsoft Excel.

excel 2007

Si está utilizando Excel 2007, lamento que PQ no esté disponible para esta versión, por lo que debe actualizar a la latest version de Excel (Excel para Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010).

Excel 2010 y Excel 2013

Para 2010 y 2013 , necesita instalar un complemento por separado que puede descargar desde este enlace y, una vez instalado, obtendrá una nueva pestaña en la cinta de Excel como se muestra a continuación:

• En primer lugar, descargue el complemento desde aquí (sitio web oficial de Microsoft).
• Una vez descargado el archivo, ábrelo y sigue las instrucciones.
• Después de eso, obtendrá automáticamente la pestaña «Power Query» en su cinta de Excel.

Si de alguna manera no aparece esta pestaña “POWER QUERY”, no hay necesidad de preocuparse por ello. Puede agregarlo usando la opción Complementos COM.

• Vaya a la pestaña Archivo ➜ Opciones ➜ Complementos.

• En las opciones de «Complementos», seleccione «Complementos COM» y haga clic en IR.
• Después de eso, marque la casilla «Microsoft Power Query para Excel».

• Al final, haga clic en Aceptar.

Excel 2016, 2019, Oficina 365

Si está utilizando Excel 2016, Excel 2019 o tiene una suscripción a OFFICE 365, ya está en la pestaña Datos , como un grupo llamado «OBTENER Y TRANSFORMAR» (me gusta ese nombre, ¿a ti?).

Mac Excel

Si usa Excel en Mac, me temo que no existe un complemento de consulta de energía para esto y solo puede actualizar una consulta existente , pero no puede crear una nueva ni editar una consulta ( ENLACE ).

Editor de consultas de energía

Power Query tiene su propio editor donde puede obtener los datos, seguir todos los pasos para crear consultas y luego cargarlos en la hoja de cálculo. Para abrir el Editor de Power Query, debe ir a la pestaña Datos y en Obtener y transformar ➜ Obtener datos ➜ Iniciar el Editor de Power Query.

A continuación se muestra una primera vista previa del editor que obtendrá cuando lo abra.

Ahora, exploremos cada sección en detalle:

1. Cinta

Veamos todas las pestañas disponibles:

• Archivo: Desde la pestaña Archivo puede cargar los datos, eliminar el editor y abrir los parámetros de consulta.
• Inicio : en la pestaña INICIO, tiene opciones para administrar los datos cargados, como eliminar y mover columnas y filas.
• Transformar : esta pestaña contiene todas las opciones que necesita para transformar y limpiar datos, como fusionar columnas, transponer, etc.
• Agregar Columna : Aquí tienes la opción de agregar nuevas columnas a los datos que tienes en el editor avanzado.
• Ver : desde esta pestaña puede realizar cambios en la vista del Editor de consultas avanzado y los datos cargados.

2. Pasos aplicados

En el lado derecho del editor, tiene un panel de configuración de consulta que incluye el nombre de la consulta y todos los pasos aplicados en una secuencia.

Cuando hace clic derecho en un paso, tiene una lista de opciones que puede hacer, como cambiar el nombre, eliminar, editar, subir o bajar, etc. y cuando haces clic en un paso, el editor te llevará a la transformación realizada en ese paso.

Mire a continuación donde ha aplicado los cinco pasos en total y cuando hago clic en el cuarto paso, me lleva a la transformación en el paso cuatro donde los nombres de las columnas no han cambiado.

3. Preguntas

El panel de consultas en el lado izquierdo enumera todas las consultas que tiene actualmente en el libro de trabajo. Básicamente es un lugar donde puedes gestionar todas las consultas.

Cuando hace clic derecho en el nombre de una consulta, puede ver todas las opciones que puede usar (copiar, eliminar, duplicar, etc.)

También puede crear una nueva consulta simplemente haciendo clic derecho en el espacio vacío en el panel de consultas y luego seleccionando la opción de fuente de datos.

4. Barra de fórmulas

Como dije, cada vez que aplicas un paso en el editor, genera un código M para ese paso y puedes ver este código en la barra de fórmulas. Puede simplemente hacer clic en la barra de fórmulas para editar el código.

Una vez que aprenda a usar el código M, también puede crear un paso escribiendo el código y simplemente haciendo clic en el botón «FX» para ingresar un paso personalizado.

5. Resumen de datos

El área de vista previa de datos parece una hoja de cálculo de Excel, pero es ligeramente diferente de una hoja de cálculo normal donde puede editar directamente una celda o datos. Cuando carga datos en el editor (lo haremos en un momento), muestra todas las columnas con los encabezados con los nombres de las columnas y luego las filas con los datos.

En la parte superior de cada columna, puede ver el tipo de datos de la columna. Cuando carga datos en el editor, Consulta avanzada aplica automáticamente el tipo de datos correcto (casi siempre) a cada columna.

Puede hacer clic en el botón en la parte superior izquierda del encabezado de la columna para cambiar el tipo de datos aplicado a la columna. Contiene una lista de todos los tipos de datos de los que puede extraer.

Y en el lado izquierdo del encabezado de la columna, tiene el botón de filtro que puede usar para filtrar los valores de la columna. Nota: Cuando filtra los valores de una columna, la consulta avanzada lo considera como un solo paso y lo enumera en Pasos aplicados.

Si hace clic derecho en el encabezado de la columna, puede ver que hay un menú que incluye una lista de opciones que puede usar para transformar los datos y usar una de las opciones y PQ que almacena como un paso en el proceso aplicado. no.

Orígenes de datos para Power Query

La mejor parte de Power Query es que tiene la capacidad de obtener datos de múltiples fuentes, transformarlos y luego cargarlos en la hoja de cálculo. Cuando hace clic en Obtener datos en GET & TRANSFORM, puede ver la lista completa de fuentes de datos que puede cargar en el editor.

Ahora veamos algunas de las fuentes de datos:

• Desde tabla/rango : con esta opción, puede cargar datos en el Editor de consultas avanzado directamente desde la hoja de trabajo activa.
• Desde libro de trabajo : Desde otro libro de trabajo que tenga en su computadora. Sólo necesita ubicar este archivo usando un cuadro de diálogo abierto y automáticamente obtendrá los datos de este archivo.
• Desde Texto/CSV : obtenga datos de un archivo de texto o un archivo separado por comas, luego podrá cargarlos en la hoja de cálculo.
• Desde carpeta : toma todos los archivos de la carpeta y admite datos en un potente editor de consultas. (Consulte esto: combine archivos de Excel de una carpeta).
• Desde Web : Con esta opción obtienes datos de una dirección web, imagina que tienes un archivo que está almacenado en la web o tienes una página web de la cual necesitas obtener los datos.

Cómo cargar datos en Power Query Editor

Ahora aprendamos cómo cargar datos en el potente editor de consultas. Aquí tenéis una lista de los nombres de los alumnos y sus puntuaciones ( ENLACE ).

Cargará datos directamente desde la hoja de cálculo, por lo que primero debe abrir el archivo y luego seguir los pasos a continuación:

• Primero, aplique una tabla de Excel a los datos (incluso si no lo hace, Excel lo hará por usted antes de cargar los datos en el editor PQ).
• Ahora, seleccione una celda en la tabla y haga clic en «Desde tabla/rango» (pestaña Obtener y transformar datos).

• Una vez que hace clic en el botón, Excel confirma el rango de datos para aplicarle una tabla de Excel.

• En este punto, tiene los datos en el Editor de Power Query y se parece a lo siguiente.

• Aquí puedes ver:
  • En la barra de fórmulas, PQ generó el código M para la tabla que acaba de cargar en el editor.
  • En el lado izquierdo del editor tienes el panel de consultas donde tienes la lista de consultas.
  • En la parte derecha, en la configuración de la consulta, tienes el apartado llamado “Pasos Aplicados” donde tienes todos los pasos listados. Nota: Debes pensar que no has realizado ningún «Tipo modificado», pero allí hay un paso llamado «Tipo modificado». Déjame contarte la INTELIGENCIA de POWER QUERY cuando cargas datos en el editor, automáticamente verifica y aplica los tipos de datos correctos para todas las columnas.

Ejemplos de Power Query (consejos y trucos)

Puedes aprender a realizar algunas de las tareas básicas que normalmente realizas con fórmulas funcionales en Excel, pero con PowerQuery puedes hacerlo con solo unos pocos clics:

Cómo cargar datos en la hoja de cálculo

Una vez que haya transformado sus datos, puede cargarlos en la hoja de cálculo y utilizarlos para análisis posteriores. En la pestaña de inicio hay un botón llamado «Cerrar y cargar» cuando haces clic en él obtienes una lista desplegable que ofrece opciones adicionales:

• Cerrar y cargar
• Cerrar y cargar

• Una vez que haga clic en el botón, se mostrarán las siguientes opciones:

• Seleccione cómo desea mostrar estos datos en su hoja de cálculo.
  • Cuadro
  • Informe de tabla dinámica:
  • Pivote de mesa
  • Crea solo una conexión
  • ¿Dónde quieres poner los datos?
    • Hoja de trabajo existente
    • Nueva hoja de trabajo.
  • Agregue estos datos al modelo de datos.
• Simplemente seleccione la opción de tabla y la nueva hoja de trabajo, deje el modelo de datos sin marcar y haga clic en Aceptar.

• En el momento en que hace clic en Aceptar, agrega una nueva hoja de trabajo con los datos.

Más ejemplos para aprender

Actualizar automáticamente una consulta

De todos los ejemplos que he citado aquí, éste es el más importante. Cuando crea una consulta, puede actualizarla automáticamente (puede configurar un temporizador).

Y aquí están los pasos:

• En la pestaña Datos, haga clic en «Consultas y conexiones» y obtendrá el panel Consultas y conexiones en el lado derecho de la ventana.

• Ahora haga clic derecho en la consulta y marque «Actualizar cada» e ingrese los minutos.

Cómo usar una fórmula y función en Power Query

Así como puedes usar funciones y fórmulas en una hoja de cálculo de Excel, Consulta avanzada tiene su propia lista de funciones que puedes usar. Los conceptos básicos de las funciones y fórmulas en PowerQuery son los mismos que los de las funciones de hoja de cálculo de Excel.

En PQ necesita agregar una nueva columna personalizada para agregar una función o fórmula.

Pongamos un ejemplo: En los datos siguientes (ya en el editor PQ) tienes el nombre y apellido ( ENLACE DE DESCARGA ) .

Imagine que necesita fusionar los dos nombres y crear una columna para el nombre completo. En este caso, puedes ingresar una fórmula simple para concatenar los nombres de las dos columnas.

• Primero, vaya a la pestaña Agregar columna y haga clic en «Columna personalizada».

• Ahora, en el cuadro de diálogo de la columna personalizada, ingrese el nombre de la nueva columna «Nombre completo» o el nombre que desee para la nueva columna.

• • La fórmula de columna personalizada es donde debe ingresar la fórmula. Entonces ingresa la siguiente fórmula :

 [First Name]&" "&[Last Name] 

• Cuando ingresa una fórmula en la «Fórmula de columna personalizada», PQ verifica la fórmula que ingresó y muestra un mensaje «No se detectaron errores de sintaxis» y, si hay un error, mostrará un mensaje de error según el tipo de error.
• Una vez que hayas ingresado la fórmula y no tenga errores, solo presiona OK.
• Ahora tiene una nueva columna al final de los datos que contiene valores de dos columnas (nombre y apellido).

Cómo utilizar una función en Power Query

De manera similar, también puede usar una función mientras agrega una columna personalizada y Power Query tiene una lista enorme de funciones que puede usar.

Entendamos cómo utilizar una función con un ejemplo fácil y sencillo. Continúo con el ejemplo anterior donde agregamos una nueva columna que combina nombre y apellido.

Pero ahora necesitas convertir el texto del nombre completo que tienes en esta columna a mayúsculas. La función que puedes utilizar es “ Text.Upper ” . Como sugiere el nombre, convierte texto a mayúsculas.

• Primero, vaya a la pestaña Agregar columna y haga clic en la columna personalizada.

• • Ahora, en el cuadro de diálogo de la columna personalizada, ingrese el nombre de la columna y la siguiente fórmula en el cuadro de fórmula de la columna personalizada:

 Text.Upper([Full Name]) 

• Y cuando haces clic en Aceptar, se crea una nueva columna con todos los nombres en mayúsculas.
• Lo siguiente es eliminar la columna anterior y cambiar el nombre de la nueva columna. Entonces haga clic derecho en la primera columna y seleccione eliminar.

• Al final, cambie el nombre de la nueva columna a «Nombre completo».

Hay un total de 700 funciones que puede usar en Power Query mientras agrega una nueva columna y aquí está la lista completa proporcionada por Microsoft para estas funciones, compruébelas.

Cómo modificar una consulta en PQ

Si desea realizar cambios en la consulta que ya está en su libro de trabajo, simplemente puede editarla y luego realizar esos cambios. En la pestaña Datos, hay un botón llamado Consultas y conexiones.

Cuando hace clic en este botón, se abre un panel en el lado derecho que enumera todas las consultas que tiene en el libro actual.

Puede hacer clic derecho en el nombre de la consulta y seleccionar editar y lo accederá al potente editor de consultas para editarlo.

Cuando edita una consulta, puede ver que todos los pasos que realizó anteriormente se enumeran en los «Pasos aplicados», que también puede editar o realizar nuevos pasos.

Y una vez que haya terminado con sus ediciones, simplemente puede hacer clic en el botón «Cerrar y cargar».

Exportar e importar conexiones

Si tiene una conexión que utilizó para una consulta y ahora desea compartir esa conexión con otra persona, puede exportar esa conexión como un archivo odc.

En la tabla de consultas hay un botón llamado «Exportar conexión» y cuando haces clic en él te permite guardar la conexión de esa consulta en tu sistema.

Y si desea importar una conexión compartida por otra persona, simplemente puede ir a la pestaña Datos y en Obtener y transformar , hacer clic en Conexiones existentes .

Y luego haga clic en el botón «Buscar más» desde donde puede ubicar el archivo de conexión que se compartió con usted e importarlo a su libro de trabajo.

Lenguaje PowerQuery (código M)

Como mencioné anteriormente, para cada paso que das en PowerQuery, genera un código (en el backend) que se llama Código M. En la pestaña Inicio, hay un botón llamado » Editor avanzado» que puedes usar para ver el código.

Y cuando haces clic en el editor avanzado, te mostrará el editor de código y este código se ve así:

M es un lenguaje que distingue entre mayúsculas y minúsculas y, como todos los demás lenguajes, utiliza variables y expresiones. La estructura básica del código se ve a continuación, donde el código comienza con la expresión LET.

En este código tenemos dos variables y los valores establecidos para ellas. Al final, para obtener el valor se utilizó la expresión IN. Ahora, cuando haga clic en Aceptar, devolverá el valor asignado a la variable «Nombre de variable» en el resultado.

Consulte este recurso para obtener más información sobre Power Query Language.

Al final

No puede permitirse el lujo de evitar POWER QUERY. Si piensas así, muchas de las cosas que hacemos con funciones de Excel o códigos VBA se pueden automatizar usándolo, y estoy seguro de que este tutorial te inspirará a usarlo cada vez más.