Power Query está integrado en las versiones de Windows de Office 365, Excel 2016, Excel 2019 y está disponible como descarga gratuita en las versiones de Windows de Excel 2010 y Excel 2013. La herramienta está diseñada para extraer, transformar y cargar datos en Excel desde un Variedad de fuentes. La mejor parte: Power Query recuerda sus pasos y los reproducirá cuando desee actualizar los datos. Esto significa que puede limpiar los datos el día 1 en el 80% del tiempo normal, y puede limpiar los datos del día 2 al 400 simplemente haciendo clic en Actualizar.
Digo esto sobre muchas características nuevas de Excel, pero esta es realmente la mejor característica que ha llegado a Excel en 20 años.
Cuento una historia en mis seminarios en vivo sobre cómo se inventó Power Query como una muleta para los clientes de SQL Server Analysis Services que se vieron obligados a usar Excel para acceder a Power Pivot. Pero Power Query siguió mejorando y todas las personas que usan Excel deberían tomarse el tiempo para aprender Power Query.
Obtener Power Query
Es posible que ya tenga Power Query. Está en el grupo Obtener y transformar en la pestaña Datos.
Pero si está en Excel 2010 o Excel 2013, vaya a Internet y busque Descargar Power Query. Sus comandos de Power Query aparecerán en una pestaña dedicada de Power Query en la cinta.
Limpiar datos por primera vez en Power Query
Para darle un ejemplo de algunas de las maravillas de Power Query, digamos que obtiene el archivo que se muestra a continuación todos los días. La columna A no está completa. Los trimestres van a través de la página en lugar de hacia abajo.
Para comenzar, guarde ese libro en su disco duro. Colóquelo en un lugar predecible con un nombre que usará para ese archivo todos los días.
En Excel, seleccione Obtener datos, Desde archivo, Desde libro.
Busque el libro de trabajo. En el panel Vista previa, haga clic en Hoja1. En lugar de hacer clic en Cargar, haga clic en Editar. Ahora verá el libro de trabajo en una cuadrícula ligeramente diferente: la cuadrícula de Power Query.
Ahora debe arreglar todas las celdas en blanco en la columna A. Si tuviera que hacer esto en la interfaz de usuario de Excel, la secuencia de comandos difícil de manejar es Inicio, Buscar y seleccionar, Ir a especial, Espacios en blanco, Igual, Flecha arriba, Ctrl + Enter .
En Power Query, seleccione Transformar, Rellenar, Abajo.
Todos los valores nulos se reemplazan con el valor anterior. Con Power Query, se necesitan tres clics en lugar de siete.
Siguiente problema: las monedas se cruzan en lugar de bajar. En Excel, puede solucionar este problema con una tabla dinámica de rango de consolidación múltiple. Esto requiere 12 pasos y más de 23 clics.
En Power Query, seleccione las dos columnas que no son trimestres. Abra el menú desplegable Unpivot Columns en la pestaña Transform y elija Unpivot Other Columns, como se muestra a continuación.
Haga clic con el botón derecho en la columna Atributo recién creada y cámbiele el nombre Trimestre en lugar de Atributo. Más de veinte clics en Excel se convierten en cinco clics en Power Query.
Ahora, para ser justos, no todos los pasos de limpieza son más cortos en Power Query que en Excel. Eliminar una columna aún significa hacer clic con el botón derecho en una columna y elegir Eliminar columna. Pero para ser honesto, la historia aquí no se trata del ahorro de tiempo en el primer día.
Pero espere: Power Query recuerda todos sus pasos
Mire en el lado derecho de la ventana de Power Query. Hay una lista llamada Pasos aplicados. Es una pista de auditoría instantánea de todos sus pasos. Haga clic en cualquier icono de engranaje para cambiar sus opciones en ese paso y hacer que los cambios pasen en cascada en los pasos futuros. Haga clic en cualquier paso para ver cómo se veían los datos antes de ese paso.
Cuando haya terminado de limpiar los datos, haga clic en Cerrar y cargar como se muestra a continuación.
Propina
Si sus datos son más de 1.048.576 filas, puede usar el menú desplegable Cerrar y cargar para cargar los datos directamente en el modelo de datos de Power Pivot, que puede acomodar 995 millones de filas si tiene suficiente memoria instalada en la máquina.
En unos segundos, sus datos transformados aparecen en Excel. Increíble.
La recompensa: datos limpios mañana con un clic
Pero, de nuevo, la historia de Power Query no se trata del ahorro de tiempo en el día 1. Cuando selecciona los datos devueltos por Power Query, aparece un panel Consultas y conexiones en el lado derecho de Excel, y en él hay un botón Actualizar. (Necesitamos un botón Editar aquí, pero como no hay uno, debe hacer clic con el botón derecho en la consulta original para ver o realizar cambios en la consulta original).
Es divertido limpiar los datos el primer día. Me encanta hacer algo nuevo. Pero cuando mi gerente ve el informe resultante y dice “Hermoso. ¿Puedes hacer esto todos los días? " Rápidamente empiezo a odiar el tedio de limpiar el mismo conjunto de datos todos los días.
Entonces, para demostrar el Día 400 de limpieza de datos, he cambiado completamente el archivo original. Nuevos productos, nuevos clientes, números más pequeños, más filas, como se muestra a continuación. Guardo esta nueva versión del archivo en la misma ruta y con el mismo nombre de archivo que el archivo original.
Si abro el libro de consulta y hago clic en Actualizar, en unos segundos, Power Query informa 92 filas en lugar de 68 filas.
Limpiar los datos del Día 2, Día 3, Día, 4,… Día 400,… Día Infinito ahora requiere dos clics.
Este único ejemplo solo rasca la superficie de Power Query. Si pasas dos horas con el libro, M es para (Data) Monkey de Ken Puls y Miguel Escobar, aprenderás sobre otras características, como estas:
- Combinando todos los archivos Excel o CSV de una carpeta en una sola cuadrícula de Excel
- Convertir una celda con Apple; Plátano; Cereza; Eneldo; Berenjena a cinco filas en Excel
- Hacer una VLOOKUP a un libro de trabajo de búsqueda mientras trae datos a Power Query
- Convertir una sola consulta en una función que se pueda aplicar a cada fila en Excel
Para obtener una descripción completa de Power Query, consulte M Is for (Data) Monkey de Ken Puls y Miguel Escobar. A finales de 2019, estará disponible la segunda edición retitulada, Master Your Data.
Gracias a Miguel Escobar, Rob García, Mike Girvin, Ray Hauser y Colin Michael por nominar a Power Query.
