En resumen: Aprenda cómo esta impresionante característica de Excel y Power BI llamada Power Query le ayudará a automatizar el proceso de importación, transformación y limpieza de sus datos para ahorrar una TONELADA de tiempo con su trabajo.
Nivel de habilidad: Principiante
Video Tutorial
Descarga los archivos de ejemplo
Los archivos CSV que uso en el video están disponibles para su descarga a continuación. Tendrá que extraer los archivos del archivo zip.
CSV Order Files For Power Query Overview.zip (337.0 KB)
Introducción a Power Query
En este tutorial proporciono una explicación introductoria de Power Query. Aprenderás por qué es mi nueva herramienta favorita de Excel para trabajar con datos, y cómo puede ayudar a automatizar procesos y ahorrarte tiempo.
La Máquina de Datos de Power Query
Estaba viendo un programa de televisión sobre cómo se hacen las cosas, y estaban explicando cómo funcionaba una máquina depositadora en una fábrica de pasteles.
Los fundamentos de una máquina depositadora son:
- Le añades ingredientes.
- Cambia algunos ajustes.
- Y mágicamente crea pasteles (galletas, rosquillas, bizcochos) que están listos para hornear.
- Agrega tus fuentes de datos (tablas de Excel, archivos CSV, tablas de bases de datos, páginas web, etc.)
- Pulsa botones en la ventana del Editor de Power Query para transformar tus datos.
- Saca esos datos a tu hoja de trabajo o modelo de datos (PowerPivot) que está listo para tablas dinámicas o informes.
Una vez ajustados los diales, se puede repetir el proceso una y otra vez para hacer pasteles perfectos cada vez. Te está entrando hambre…? 🙂
¡Power Query funciona de forma muy similar!
Power Query es como una máquina porque una vez que tiene su consulta configurada, el proceso se puede repetir con el clic de un botón (actualizar) cada vez que sus datos cambian.
Si ha utilizado macros para transformar sus datos, puede pensar en esto como una alternativa mucho más fácil a VBA que NO requiere codificación.
Tareas de datos comunes hechas fáciles
¿Trabaja con datos que han sido exportados desde un sistema de registro? Podría tratarse de un libro mayor, contabilidad, ERP, CRM, Salesforce.com o cualquier sistema de informes que contenga datos.
Si es así, probablemente pase mucho tiempo transformando o reformando sus datos para crear informes, tablas dinámicas o gráficos adicionales.
Estas transformaciones de datos podrían incluir tareas como:
- Quitar columnas, filas, espacios en blanco
- Convertir tipos de datos – texto, números, fechas
- Dividir o fusionar columnas
- Ordenar & filtrar columnas
- Añadir columnas calculadas
- Agregar o resumir datos
- Buscar & reemplazar texto
- Desplegar datos para utilizarlos en tablas dinámicas
¿Algunas de estas tareas te suenan? Si es así, probablemente también te suenen aburridas, repetitivas y que requieren mucho tiempo 🙂 Créeme, he pasado la mayor parte de mi carrera haciendo estas tareas e intentando encontrar formas más rápidas de realizarlas.
Afortunadamente, Power Query tiene botones que automatizan todas estas tareas!
Resumen de la cinta de Power Query
A partir de Excel 2016 para Windows, Power Query se ha integrado completamente en Excel. Ahora se encuentra en la pestaña Datos de la cinta de opciones en el grupo Obtener & Transformación.
En Excel 2010 y 2013 para Windows, Power Query es un complemento gratuito. Una vez instalado, la pestaña Power Query será visible en la cinta de opciones de Excel.
Utiliza los botones de la pestaña Datos o Power Query para obtener sus datos de origen. Una vez más, sus datos podrían estar almacenados en archivos de Excel, archivos csv, Access, base de datos de SQL server, SharePoint, Salesforce.com, Dynamics CRM, Facebook, Wikipedia, sitios web, y más.
Una vez que haya especificado de dónde provienen sus datos, entonces utiliza la ventana del Editor de Power Query para hacer transformaciones a los datos.
Los botones de la ventana del Editor de Power Query te permiten transformar tus datos.
Piensa en algunas de esas tareas que realizas repetidamente al navegar por los botones de la imagen anterior. Cada vez que presiona un botón se registran sus acciones (pasos), y puede volver a aplicar rápidamente los pasos cuando reciba nuevos datos actualizando la consulta.
Después de completar sus pasos, puede dar salida a los datos en una Tabla de su libro de Excel haciendo clic en el botón Cerrar & Cargar.
También puede modificar las consultas existentes y actualizar sus tablas de salida con los cambios o datos actualizados.
Ejemplos de transformación de datos
Aquí hay algunos ejemplos de lo que Power Query puede hacer con sus datos.
Despivotar datos para tablas dinámicas
Mi característica favorita de Power Query es su capacidad para despivotar datos. Esta es una técnica utilizada para obtener sus datos listos para la fuente de una tabla dinámica. Esto también se conoce como la normalización de sus datos para obtener en un formato tabular.
Los datos podrían empezar a parecer algo como lo siguiente.
Y usted quiere que el resultado final se vea así.
Power Query puede hacer esto con el clic de unos pocos botones, y preparar sus datos para usarlos en una tabla dinámica.
Aquí hay un artículo y un vídeo sobre cómo descomponer los datos con Power Query.
Visite mi artículo sobre cómo estructurar los datos de origen para una tabla pivotante si no está familiarizado con la razón por la que sus datos deben tener este aspecto para una tabla pivotante.
Apostar (combinar) tablas con Power Query
La función de apuntar de Power Query le permite combinar varias tablas (apilarlas verticalmente) para crear una tabla grande. Puede hacer esto con múltiples tablas en un archivo, o puede tirar de los datos de un montón de diferentes archivos/fuentes.
Digamos que tiene una carpeta que contiene archivos CSV o Excel con datos de informes para cada mes. Eche todos esos archivos en la máquina de Power Query, y le escupirá una bonita tabla que podrá utilizar para crear tablas dinámicas y gráficos.
Si los datos de esos informes también necesitan ser transformados (eliminar filas, dividir columnas, despivotar, etc.), entonces Power Query puede manejar eso en el mismo proceso.
Una vez que está configurado, todo lo que tiene que hacer es pulsar el botón de actualización cada mes cuando se añade un nuevo archivo a la carpeta y las filas se añadirán a su tabla de salida.
¡Qué impresionante es eso! 🙂
Fusionar tablas – Una alternativa VLOOKUP
Power Query tiene la capacidad de fusionar o unir tablas. Esto puede ser utilizado como una alternativa a las fórmulas VLOOKUP o INDEX/MATCH.
Digamos que tiene esta tabla de datos de registros de ventas, y está utilizando un VLOOKUP para traer información sobre el producto basado en el nombre del producto vendido. La información de su grupo de productos se encuentra en otra tabla en una hoja o libro de trabajo diferente.
Usar fórmulas VLOOKUP es genial, pero a menudo puede significar añadir miles de fórmulas a su libro de trabajo. Lo que aumenta el tamaño del archivo y el tiempo de cálculo.
Power Query hace que sea muy rápido y fácil fusionar dos tablas con sólo unos pocos clics. Básicamente utiliza uniones SQL, por lo que incluso puedes hacer fusiones más avanzadas como uniones internas, externas, izquierdas, derechas, completas y anti uniones.
Crear funciones personalizadas
Power Query fue diseñado para que NO tengas que saber codificar para usarlo. Es muy fácil de usar porque sólo tiene que hacer clic en los botones y aplicar filtros como lo haría normalmente en Excel.
Sin embargo, Power Query puede ser programado para crear funciones personalizadas. Esto le da un potencial aparentemente ilimitado para transformar sus datos de casi cualquier manera posible.
Se basa en el lenguaje M, y la mayoría de las funciones son muy similares a escribir una fórmula en Excel. Esto también hace que sea más fácil de usar y de aprender el código.
Este nuevo lenguaje y conjunto de funciones significa que hay mucho que aprender, pero considero que es la parte divertida y desafiante. Además, los empleadores del futuro definitivamente buscarán empleados con conocimientos de Power Query.
Power Query registra tus pasos & Automatiza los procesos
Power Query no sólo facilita todas estas tareas, sino que también registra tus pasos para que NO tengas que hacerlos una y otra vez. Te ahorrará mucho tiempo si preparas los mismos datos todos los días, semanas o meses.
También hace un trabajo bastante bueno en el manejo de errores. Si la estructura de sus datos fuente cambia, Power Query le dirá en qué paso se rompió y le permitirá arreglarlo. Esto hace que el mantenimiento sea fácil y no tenga que rehacer completamente su proceso cuando algo cambia.
Puede utilizar Power Query para preparar sus datos para su uso en tablas dinámicas, gráficos e informes de cuadros de mando. Este es un paso crítico en el proceso de resumir y analizar datos.
La máquina de Power Query & Power BI
Bueno, no puede hacer galletas exactamente, pero Power Query es una herramienta bastante impresionante. Te ahorrará un montón de tiempo a la hora de transformar tus datos.
Power Query es sólo una pieza de la suite de productos Power BI (Business Intelligence) de Microsoft.
Si volvemos a la analogía de hornear galletas en una fábrica, puedes pensar en Power Query como el primer paso de la cadena de montaje. Una vez formadas las galletas, hay que hornearlas (Pivot Tables, PowerPivot) y luego empaquetarlas para su presentación (Power View, Power Map, Charts, Dashboards, etc.)
Puedes pensar en Excel como la nave industrial que alberga todas estas herramientas. ¡La parte emocionante es que hay un futuro muy brillante para Excel! Todos estos avances tecnológicos nos ayudarán a dar sentido a nuestros datos de nuevas maneras, a ahorrarnos tiempo y a impresionar a nuestros jefes 🙂
¿Cómo obtengo Power Query?
La otra parte buena es que Power Query está ahora integrado en Excel a partir de Excel 2016 para Windows. Si estás en Excel 2010 o 2013 entonces Power Query es un complemento gratuito.
Tengo una página dedicada que te ayudará a determinar si tienes la versión correcta de Excel para obtener Power Query. También proporciona instrucciones completas de instalación y el enlace de descarga.
Guía completa para instalar Power Query
Para que te hagas una idea de la importancia de esta herramienta, Power Query se integró completamente en Excel en Excel 2016 para Windows, y se encuentra en la pestaña Datos de la cinta de opciones.
También se conoce como Obtener & Transformación, aunque el término Power Query es el más común.
Recursos adicionales
Este artículo ha proporcionado una visión general de los fundamentos de Power Query que debería ayudarte a entender algunas de las principales características. Power Query tiene una tonelada de características y definitivamente hay mucho que aprender.
Estaré compartiendo más artículos y videos de cómo hacerlo en las próximas semanas. Aquí hay algunos recursos que le ayudarán a empezar.
Cómo Unpivot Your Data with Power Query + Video Tutorial
Free Training Webinar on the Power Tools
Ahora mismo estoy llevando a cabo un webinar de formación gratuito sobre todas las Power Tools en Excel. Esto incluye Power Query, Power Pivot, Power BI, tablas dinámicas, macros & VBA, y más.
Se llama The Modern Excel Blueprint. Durante el webinar explico qué son estas herramientas y cómo pueden encajar en tu flujo de trabajo.
También aprenderás a convertirte en el Héroe de Excel de tu organización, ese chico o chica en el que todo el mundo confía para obtener ayuda de Excel y proyectos divertidos.
El webinar se imparte en múltiples días y horarios. Por favor, haz clic en el siguiente enlace para registrarte y guardar tu asiento.
Haz clic aquí para registrarte en el webinar gratuito