Cómo expandir columnas dinámicamente en Power Query
Expandir las columnas de la tabla es una acción común de Power Query. Incluso tiene su propio icono. Pero aquí acecha un problema. Al hacer clic en el botón expandir datos, se codifican los nombres de las columnas en el código M. Por lo tanto, si se agregan nuevas columnas a la fuente en una fecha posterior, no aparecen. Entonces, en esta publicación, veremos cómo expandir columnas dinámicamente en Power Query para obtener todos los datos que queremos.
Tabla de contenido
- El problema
- Los datos
- Conexión a los datos
- Expandiendo la columna
- Ejemplo de problema
- Usando Table.Combine para expandir columnas dinámicamente
- Usando Table.Combine mientras se conservan las columnas
- Cómo obtener los nombres de columna correctos
- Conclusión
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre de archivo: 0141 Expandir columnas dinámicamente.zip
Tener acceso
El problema
Echemos un vistazo a un ejemplo sencillo para ver el problema.
Los datos
Nuestro libro de Excel incluye 4 hojas de trabajo: Q1 , Q2 , Q3 y Q4 .
Cada hoja de trabajo contiene 4 columnas: Fecha , Artículo , Región y Valor .
Aquí están los datos de ejemplo de la hoja de trabajo del primer trimestre :
Conexión a los datos
Cuando nos conectamos al libro de trabajo, en el cuadro de diálogo de Navegación, seleccionamos la carpeta para importar todas las hojas de trabajo.
La ventana Vista previa muestra todas las hojas de trabajo en una sola tabla.
Por el momento, sólo queremos la columna Datos . Entonces, seleccione la columna Datos , luego haga clic en Inicio Quitar columnas (menú desplegable) Quitar otras columnas .
Expandiendo la columna
Para expandir los datos, haga clic en el ícono de dos flechas en la parte superior de la columna Datos . Queremos todas las columnas y no queremos incluir el nombre de la columna original como prefijo.
Fantástico, ahora tenemos todos los datos ampliados.
Ejemplo de problema
Ahora, regresemos y agreguemos una nueva columna al archivo fuente.
En la pestaña Q3 , agregué una columna Tamaño .
Cuando actualizamos los datos, la nueva columna no aparece.
Mirar el código M para el paso de Datos ampliados revela el problema. El código generado automáticamente por Power Query es:
= Table.ExpandTableColumn(Source, "Data", {"Column1","Column2","Column3","Column4"}, {"Column1", "Column2", "Column3", "Column4"})
Los nombres de las columnas se han codificado en el paso. Por lo tanto, esto nunca funciona cuando se agregan nuevos datos.
Si cambiamos manualmente el código M para incluir "Columna5" , funcionará. Pero no es una gran solución. No queremos editar consultas manualmente cada vez que haya cambios.
Creemos una solución dinámica.
Usando Table.Combine para expandir columnas dinámicamente
Table.Combine es una función de código M que (sí, lo adivinaste) combina tablas. La buena noticia es que la función no requiere que codifiquemos ningún nombre de columna; los recoge a todos.
Elimine el paso Datos ampliados . Haga clic en el ícono fx en la barra de fórmulas, luego ingrese lo siguiente:
= Table.Combine(#"Removed Other Columns"[Data])
- #”Otra columna eliminada” es el nombre del paso anterior
- [Datos] se refiere a la columna Datos que queremos expandir.
¡AUGE! Eso es todo lo que hizo falta.
Ahora la ventana de vista previa muestra todas las columnas de todas las Tablas; incluyendo la nueva columna.
Obtenga más información sobre Table.Combine aquí: https://learn.microsoft.com/en-us/powerquery-m/table-combine
Usando Table.Combine mientras se conservan las columnas
Antes de empezar a celebrar, Table.Combine también tiene un problema. No conserva ninguna de las columnas existentes.
Vuelva al paso donde eliminamos las otras columnas; cámbielo para que se mantengan las columnas Nombre y Datos .
Tan pronto como volvemos al paso Table.Combine, la columna Nombre desaparece. Esto se debe a que las columnas anteriores no se conservan.
Esto significa que es hora de unir nuestros conocimientos y crear la solución perfecta.
Cambie el paso Table.Combine a lo siguiente:
= Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data]))
Table.Combine nos proporciona todas las columnas. Table.ColumnNames nos proporciona la lista de nombres de esas columnas.
Copie el código en la barra de fórmulas (excluyendo el signo igual). Luego elimine el paso.
Ahora, hagamos clic en el ícono de expandir como de costumbre.
En el código M, podemos reemplazar las primeras instancias de esto:
{"Column1", "Column2", "Column3", "Column4"}
Con este el código que copiamos de arriba:
Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data]))
Al observar la documentación de Table.ExpandTableColumn, se revela que la segunda sección de nombres de columnas es opcional. Por lo tanto, podemos eliminar la segunda instancia de:
{"Column1", "Column2", "Column3", "Column4"}
La función de código M final para este paso será:
= Table.ExpandTableColumn(#"Removed Other Columns", "Data",Table.ColumnNames(Table.Combine(#"Removed Other Columns"[Data])))
La ventana de vista previa muestra las columnas retenidas y la nueva columna. ¡ASOMBROSO!
Encuentra mas sobre:
- Table.ColumnNames: https://learn.microsoft.com/en-us/powerquery-m/table-columnnames
- Tabla.ExpandTableColumn: https://learn.microsoft.com/en-us/powerquery-m/table-expandtablecolumn
Cómo obtener los nombres de columna correctos
Nuestros datos provienen de hojas de cálculo de Excel separadas. Debido a que es una fuente no estructurada, las columnas reciben los nombres de Columna1, Columna2, Columna3, etc. ¿No sería mejor tener los nombres de columna correctos del conjunto de datos original?
Para ello, debemos promocionar los encabezados dentro de cada Tabla.
Seleccione el paso antes de expandir los datos. En nuestro ejemplo, se llama Otra columna eliminada .
Haga clic en el ícono fx al lado de la barra de fórmulas para crear un nuevo paso e ingrese lo siguiente:
= Table.TransformColumns(#"Removed Other Columns",{{"Data", each Table.PromoteHeaders(_), type text}})
Esta función promueve encabezados dentro de cada tabla en la columna Datos antes de expandirlos.
Ahora, cuando expandimos la tabla, tenemos los nombres de columna correctos.
Conclusión
De forma predeterminada, la expansión de columnas en Power Query usa la función Table.ExpandTableColumn . Esta función codifica los nombres de las columnas. Por lo tanto, si los nombres de las columnas cambian o se agregan nuevas columnas, nuestros datos pueden estar incompletos o causar errores.
Si utilizamos la función Table.Combine ; incluye todas las columnas nuevas, pero no conserva ninguna columna existente.
Pero pudimos usar ambas funciones juntas, junto con Table.ColumnNames , para crear una solución completamente dinámica.
Artículos Relacionados:
- Power Query: obtenga datos cuando cambien los nombres de la hoja/tabla (2 formas)
- Anexar Power Query: combine rápidamente muchas consultas en 1
- Power Query: valor de búsqueda en otra tabla con combinación
Deja una respuesta