Anexar Power Query: combine rápidamente muchas consultas en 1
Los datos no siempre vienen en un solo archivo o de una sola descarga. Por lo tanto, tenemos que encontrar una manera de unir varios archivos para poder usarlos como si fuera una única fuente de datos. Estos pueden ser informes de centros de costos, extractos de datos mensuales, perfiles de productos, datos de encuestas, etc. Básicamente, se aplica a cualquier dato que provenga de múltiples fuentes. Para combinar estos orígenes, utilizamos la transformación de anexos de Power Query.
La transformación de anexos de Power Query nos permite combinar consultas con un diseño de columna similar en una sola consulta. Además, no olvide que actualizamos todas las fuentes de datos con un solo clic en Actualizar datos todos . Al usar la transformación de anexos, podemos poner El fin de las tediosas rutinas de copiar y pegar para combinar varios archivos.
Si busca combinar fuentes de datos buscando valores desde la fuente, necesita la transformación de combinación. Encuentre información sobre la transformación de fusión aquí .
Tabla de contenido
- Guión
- Crear la primera consulta
- Duplicar y editar la consulta.
- Agregar consultas
- Agregar otra consulta
- Cuando los encabezados de las columnas son diferentes
- Combinar archivos de diferentes tipos/estructuras de datos
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0104 Power Query Append.zip
Tener acceso
Los ejemplos de esta publicación utilizan los siguientes archivos:
- enero 2019.xlsx
- febrero 2019.xlsx
- marzo 2019.xlsx
- Abril 2019.xlsx
Seguiremos todos los pasos de principio a fin, así que encienda Excel y comencemos.
Guión
El ejemplo en el que estamos trabajando combina varios períodos de datos de ventas en una sola consulta.
La siguiente captura de pantalla muestra las primeras filas del archivo.xlsx de enero de 2019. Los otros archivos siguen una estructura de columnas similar.
En realidad, sería poco probable que utilicemos este enfoque para este tipo de escenario. Como los datos de origen tienen un diseño coherente, es más probable que combinemos todos los archivos en una carpeta. Tendemos a utilizar la transformación de anexos cuando los datos provienen de fuentes con un diseño inconsistente (por ejemplo, un informe contable financiero nominal y una hoja de cálculo presupuestaria). Luego, remodelamos cada fuente de datos individualmente antes de combinarlas en una sola consulta. Sin embargo, para demostrar la técnica, este enfoque funcionará bien.
Crear la primera consulta
Abra un nuevo libro de Excel; aquí será donde se cargarán los datos combinados.
Haga clic en Datos Obtener datos del archivo del libro de trabajo .
Navegue hasta el archivo.xlsx de enero de 2019 desde las descargas y haga clic en Importar .
En la ventana del Navegador , seleccione el libro que contiene los datos (la hoja de trabajo de enero en nuestro ejemplo) y luego haga clic en Transformar datos .
Se abrirá el editor de Power Query y se mostrará una vista previa de los datos.
Nota: Para este ejemplo, asumo que la configuración predeterminada se aplica dentro de Power Query. Por lo tanto, los pasos Encabezados promocionados y Tipo modificado se implementan automáticamente.
Necesitamos algunas transformaciones simples para convertir esto en un formato utilizable.
- Elimine las dos filas superiores haciendo clic en Inicio Quitar filas Quitar filas superiores. Ingrese 2 en la ventana Quitar filas superiores y haga clic en Aceptar .
- Promocione la primera fila de datos al encabezado haciendo clic en Transformar Usar la primera fila como encabezados
- Agregue una columna de fecha de fin de mes seleccionando la columna Fecha y luego haga clic en Agregar columna Fecha Mes Fin de mes
- Con la columna Fecha aún seleccionada, agregue una columna de número de semana haciendo clic en Agregar columna Fecha Semana Semana del año
- Cambie el nombre de la columna Semana del año a Semana
- Mueva la columna Fin de mes al inicio haciendo clic derecho en el encabezado de la columna Fin de mes y seleccione Mover al principio en el menú.
- Compruebe que se hayan aplicado los siguientes tipos de datos; si no, aplíquelos manualmente.
- Fin de mes = Fecha
- Fecha = Fecha
- Cliente = Texto
- Producto = Texto
- Vendido por = Texto
- Valor = Número entero
- Semana = Número entero
Después de los pasos de transformación anteriores, la ventana de vista previa debería verse así:
Esas son todas las transformaciones que necesitamos por ahora. Carguemos esto solo como conexión.
Haga clic en Inicio Cerrar y cargar en... en la cinta.
En el cuadro de diálogo Importar datos , seleccione Sólo crear conexión y luego haga clic en Aceptar .
El panel Conexión de consultas muestra solo la consulta de enero (haga clic en Conexiones de consultas de datos en la cinta si este panel no está visible en Excel). No se carga nada en la hoja de trabajo.
Duplicar y editar la consulta.
Para nuestro ejemplo, no queremos volver a realizar los mismos pasos de transformación. Por lo tanto, duplicaremos la consulta de enero y luego cambiaremos los pasos para que funcionen con el archivo February.xlsx .
Abra el editor de Power Query haciendo doble clic en la consulta de enero en el panel Conexiones de consultas .
En Power Query, haga clic derecho en la consulta de enero en el panel Consultas y seleccione Duplicar .
En el panel Configuración de consulta, cambie el nombre de la consulta duplicada a Febrero .
Los primeros dos pasos de nuestro ejemplo tienen codificado "enero" dentro del código M, por lo que debemos editar estos pasos. Por lo tanto, debemos cambiar cualquier referencia de enero a febrero . La forma de editar estos pasos depende de usted. Haga clic en icono de configuración, al lado del paso, o edite el código M directamente en la barra de fórmulas o el editor avanzado.
En términos del código M, los cambios son los siguientes:
Fuente:
= Excel.Workbook(File.Contents("C:UsersmarksPower Query ExamplesJanuary 2019.xlsx"), null, true)
Se convierte en:
= Excel.Workbook(File.Contents("C:UsersmarksPower Query ExamplesFebruary 2019.xlsx"), null, true)
Navegación:
= Source{[Item="January",Kind="Sheet"]}[Data]
Se convierte en:
= Source{[Item="February",Kind="Sheet"]}[Data]
Eso es todo. Haga clic en Cerrar y cargar en... para cargar la consulta de febrero como Crear conexión únicamente .
Las Consultas y Conexiones ahora mostrarán las consultas de enero y febrero.
Agregar consultas
La transformación de anexos de Power Query es razonablemente sencilla.
Abra el editor de Power Query. Luego, haga clic en Inicio Agregar consultas (desplegable) Agregar consultas como nuevas
Se abre el cuadro de diálogo Agregar . Hay dos vistas posibles en este cuadro de diálogo:
- Vista para combinar dos consultas
- Vista para combinar tres o más consultas
Ambas vistas son sencillas de usar, como se muestra a continuación.
Vista para combinar dos consultas
En esta vista, seleccione las dos consultas que se combinarán en los cuadros desplegables. La tabla Primaria siempre aparece primero en la consulta combinada.
Vista para combinar tres o más consultas.
En esta vista, seleccione las consultas en el panel izquierdo y haga clic en Agregar para moverlas a la lista de tablas para agregar.
Desde cualquier vista, después de hacer clic en Aceptar , se crea una nueva consulta. Como se muestra a continuación, ambas consultas ahora se combinan en una sola tabla.
Probablemente la consulta reciba el nombre predeterminado Append1; asegúrese de darle un nombre más significativo, como Datos de ventas combinados .
La función de código M que combina las tablas es Table.Combine. Obtenga más información sobre esta función aquí: https://learn.microsoft.com/en-us/powerquery-m/table-combine
Haga clic en Cerrar y cargar para cargar la consulta en una nueva hoja de trabajo como una tabla.
Eche un vistazo a la nueva tabla en Excel. Contiene datos de enero y febrero. Para ilustrar esto, agregué una tabla dinámica que muestra el final del mes, la semana y los valores.
Ahora hay tres consultas dentro del libro de trabajo; Enero, febrero y datos de ventas combinados.
Agregar otra consulta
A continuación, intente agregar el archivo de marzo de 2019.xlsx por su cuenta. Siga los mismos pasos anteriores para crear la conexión, luego edite la consulta combinada para incluir los datos de marzo. Debe usar la opción de tres o más tablas en el cuadro de diálogo Agregar.
Después de agregar marzo, los datos y la tabla dinámica se ven así:
¿Entiendo? Todo eso parece relativamente sencillo, ¿no?
Cuando los encabezados de las columnas son diferentes
Ahora veamos el archivo April.xlsx . Es ligeramente diferente a los demás; el encabezado de la columna Clientes ha cambiado a Vendido a . Estos pequeños cambios ocurren regularmente cuando se trata de fuentes de datos manuales. Desafortunadamente, otros modifican los archivos sin darse cuenta de que esto afecta nuestro proceso.
Si seguimos los mismos pasos anteriores, la consulta se interrumpe en cualquier acción en la que el encabezado de la columna Clientes esté codificado dentro del código M.
Power Query no puede encontrar una columna llamada Cliente . Como sabemos, la columna Clientes ahora se llama Vendido a en el archivo fuente.
Para este ejemplo, adoptaremos un enfoque simple. Haga clic en los pasos de arriba a abajo; Cada vez que identifique un paso que cause el error, actualice el código M en la barra de fórmulas para hacer referencia a Vendido a en lugar de Cliente .
Si el error ocurre en un paso de Tipo cambiado, cambie este código:
= Table.TransformColumnTypes(#"Promoted Headers1",{{"Date", type date}, {"Customer", type text}, {"Product", type text}, {"Sold By", type text}, {"Value", Int64.Type}})
A esto:
= Table.TransformColumnTypes(#"Promoted Headers1",{{"Date", type date}, {"Sold To", type text}, {"Product", type text}, {"Sold By", type text}, {"Value", Int64.Type}})
Si el error ocurre en el paso Columnas reordenadas, cambie esto:
= Table.ReorderColumns(#"Renamed Columns",{"End of Month", "Date", "Customer", "Product", "Sold By", "Value", "Week"})
A esto:
= Table.ReorderColumns(#"Renamed Columns",{"End of Month", "Date", "Sold To", "Product", "Sold By", "Value", "Week"})
Después de aplicar los cambios anteriores, la consulta debería funcionar nuevamente.
Siga las instrucciones anteriores para agregar la consulta de abril a la consulta de Datos de ventas combinados .
Todavía hay un problema. Mire la captura de pantalla a continuación.
Las columnas Cliente y Vendido a eran la tercera columna en las consultas relevantes, pero aparecen como dos columnas separadas.
Este es un punto crucial a tener en cuenta. Power Query utiliza los encabezados de columna para determinar qué columnas combinar, no la posición dentro de la consulta.
Necesitamos dar a las consultas títulos de columna consistentes. En la consulta de abril , cambie el nombre del encabezado de la columna Vendido a a Cliente .
Ahora las columnas deberían combinarse correctamente.
Cierre y cargue los datos nuevamente en Excel. Abril es la única consulta nueva, así que cárguela como Sólo conexión .
Combinar archivos de diferentes tipos/estructuras de datos
Vimos anteriormente que al agregar consultas, las columnas con el mismo encabezado de columna se combinan en la misma columna. Por lo tanto, podemos combinar datos de cualquier fuente en una consulta siempre que les proporcionemos los mismos encabezados de columna.
Cuando todos los archivos tienen una estructura consistente, podemos automatizar esto aún más combinando todos los archivos en una carpeta .
La documentación de Microsoft sobre la transformación de anexos de Power Query se puede encontrar aquí: https://support.microsoft.com/en-us/office/append-queries-power-query-e42ca582-4f62-4a43-b37f-99e2b2a4813a
Leer más publicaciones en esta serie
- Introducción a Power Query
- Obtener datos en Power Query: cinco fuentes de datos comunes
- DataRefresh Power Query en Excel: 4 formas de opciones avanzadas
- Utilice el editor de Power Query para actualizar consultas
- Conozca las opciones de Power Query Cerrar Carga
- Parámetros de Power Query: 3 métodos
- Transformaciones comunes de Power Query (más de 50 transformaciones poderosas explicadas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Obtenga datos de una carpeta en Power Query: combine archivos rápidamente
- Listar archivos en una carpeta subcarpetas con Power Query
- Cómo obtener datos del libro actual con Power Query
- Cómo desvincular en Excel usando Power Query (3 formas)
- Power Query: valor de búsqueda en otra tabla con combinación
- Cómo cambiar la ubicación de los datos de origen en Power Query (7 formas)
- Fórmulas de Power Query (cómo usarlas y errores que se deben evitar)
- Declaración If de Power Query: condiciones anidadas ifs múltiples
- Cómo utilizar Power Query Group By para resumir datos
- Cómo utilizar las funciones personalizadas de Power Query
- Power Query: errores comunes, cómo solucionarlos
- Power Query: consejos y trucos
Deja una respuesta