¿Cómo usar Power Query en Excel?
Excel Power Query se utiliza para buscar fuentes de datos, hacer conexiones con fuentes de datos y luego dar forma a los datos de acuerdo con nuestros requisitos de análisis. Una vez que hayamos terminado con la configuración de los datos según nuestras necesidades, también podemos compartir nuestros hallazgos y crear varios informes utilizando más consultas.
Pasos
Básicamente, hay 4 pasos, y el orden de estos 4 pasos en Power Query es el siguiente:
- Conexión: primero nos conectamos a los datos, que pueden estar en algún lugar, en la nube, en servicio o localmente.
- Transformar: el segundo paso sería cambiar la forma de los datos según los requisitos del usuario.
- Combinar: en este paso, realizamos algunos pasos de transformación y agregación y combinamos datos de ambas fuentes para producir un informe combinado.
- Administrar: esto combina y agrega columnas en una consulta con columnas coincidentes en otras consultas en el libro de trabajo.
Hay muchas funciones superpoderosas de Excel Power Query.
Supongamos que tenemos datos de compra de los últimos 15 años en 180 archivos. Ahora bien, la gestión de una organización requeriría consolidar los números antes de analizarlos. La administración puede tomar cualquiera de los siguientes métodos:
- Abrirían todos los archivos y los copiarían y pegarían en un archivo.
- Por otro lado, pueden usar una solución inteligente, que es aplicar fórmulas, ya que es propensa a errores.
Cualquiera que sea el método que elijan, contiene mucho trabajo manual y, después de unos meses, habrá nuevos datos de ventas para la duración adicional. Tendrán que volver a hacer el mismo ejercicio.
Sin embargo, Power Query puede ayudarlos a no hacer este trabajo tedioso y repetitivo. Entendamos esta consulta de energía de Excel con un ejemplo.
Ejemplo
Supongamos que tenemos archivos de texto en una carpeta con datos de ventas y queremos obtener esos datos en nuestro archivo de Excel.
Como podemos ver en la imagen de abajo, tenemos dos tipos de archivos en la carpeta, pero queremos obtener los datos de solo archivos de texto en el archivo de Excel.
Para hacer lo mismo, los pasos serían:
Paso 1: Primero, necesitamos obtener los datos en Power Query para que podamos realizar los cambios necesarios en los datos para importarlos a un archivo de Excel.
Para hacer lo mismo, escogeremos la opción “Desde carpeta” del menú “Desde archivo” después de hacer clic en el comando “Obtener datos” del grupo “Obtener y transformar” en la pestaña “Datos” .
Paso 2: selecciona la ubicación de la carpeta navegando.
Haga clic en 'Aceptar'
Paso 3: Se abrirá un cuadro de diálogo que contiene la lista de todos los archivos en la carpeta seleccionada con los encabezados de columna como 'Contenido', 'Nombre', 'Extensión', 'Fecha de acceso', 'Fecha de modificación', 'Fecha de creación', 'Atributos' y 'Ruta de carpeta'.
Hay 3 opciones, es decir, Combinar , Cargar y Transformar datos .
- Combinar : Esta opción se utiliza para ir a una pantalla donde podemos elegir qué datos combinar. El paso de edición se omite para esta opción y no nos da control sobre qué archivos combinar. La función de combinación toma todos los archivos de la carpeta para consolidar, lo que puede dar lugar a errores.
- Cargar: esta opción simplemente cargará la tabla como se muestra arriba en la imagen en la hoja de cálculo de Excel en lugar de los datos reales en los archivos.
- Transformar datos: a diferencia del comando 'Combinar' , si usamos este comando, entonces podemos elegir qué archivos combinar, es decir, podemos combinar solo un tipo de archivo (la misma extensión).
Como en nuestro caso, queremos combinar solo archivos de texto (.txt); Elegiremos el comando “Transformar datos” .
Podemos ver "Pasos aplicados" en el lado derecho de la ventana. Por ahora, solo hay un paso que es tomar los detalles de los archivos de la carpeta.
Paso 4: Existe una columna llamada 'Extensión' donde podemos ver que los valores de la columna están escritos en ambos casos, es decir, mayúsculas y minúsculas.
Sin embargo, necesitamos convertir todos los valores a minúsculas ya que el filtro diferencia entre ambos. Para hacer lo mismo, debemos seleccionar la columna y luego elegir "Minúsculas" en el menú del comando "Formato" .
Paso 5: Filtraremos los datos usando la columna 'Extensión' para archivos de texto.
Paso 6: Necesitamos combinar datos para ambos archivos de texto ahora usando la primera columna 'Contenido'. Haremos clic en el icono situado a la derecha del nombre de la columna.
Paso 7: Se abrirá un cuadro de diálogo titulado 'Combinar archivos' donde debemos seleccionar el delimitador como 'Tab' para los archivos de texto (archivos con la extensión '.txt' ') y podemos elegir la base para la detección del tipo de datos. Y haga clic en 'Aceptar'.
Después de hacer clic en 'Aceptar' , obtendremos los datos combinados de los archivos de texto en la ventana 'Power Query' .
Podemos cambiar el tipo de datos de las columnas según sea necesario. Para la columna 'Ingresos' , cambiaremos el tipo de datos a 'Moneda'.
Podemos ver los pasos aplicados a los datos usando una consulta de energía en el lado derecho de la ventana.
Después de realizar todos los cambios necesarios en los datos, podemos cargar los datos en una hoja de cálculo de Excel usando el comando 'Cerrar y cargar en' debajo del grupo 'Cerrar' en la pestaña 'Inicio' .
Necesitamos elegir si queremos cargar los datos como una tabla o una conexión. Luego haga clic en 'Aceptar'.
Ahora podemos ver los datos como una tabla en la hoja de trabajo.
Y el panel 'Consultas del libro de trabajo' en el lado derecho, que podemos usar para editar, duplicar, fusionar, agregar consultas y para muchos otros propósitos.
Excel Power Query es muy útil, ya que podemos ver que se han cargado 601,612 filas en unos pocos minutos.
Cosas para recordar
- Power Query no cambia los datos de origen originales. En lugar de cambiar los datos de origen originales, registra cada paso que realiza el usuario mientras conecta o transforma los datos, y una vez que el usuario completa la configuración de los datos, toma el conjunto de datos refinados y lo lleva al libro de trabajo.
- Power Query distingue entre mayúsculas y minúsculas.
- Mientras consolidamos los archivos en la carpeta especificada, debemos asegurarnos de usar la columna 'Extensión', y debemos excluir los archivos temporales (que tienen la extensión '.tmp' y el nombre de estos archivos comienza con el signo '~') como Power Query también puede importar estos archivos.
