Cómo combinar filas en Power Query
En esta publicación, veremos cómo combinar filas con Power Query. La interfaz de usuario nos proporciona una manera de combinar columnas, pero no hay ninguna transformación de filas de combinación de Power Query. Por eso, te mostraré cómo lograrlo en este post.
Hay dos escenarios comunes en los que necesitamos fusionar filas:
- Fusionar todas las filas del conjunto de datos
- Fusionar filas específicas (por ejemplo, filas de encabezado)
Ambos están cubiertos en esta publicación.
Tabla de contenido
- Power Query combina filas en todo el conjunto de datos
- Guión
- Cargar datos en Power Query
- Fusionar columnas
- Agrupar por con suma
- Editar el código M
- Cargar datos en Excel
- Power Query combina filas específicas
- Guión
- Convertir los datos en una tabla.
- Cargue los datos en Power Query
- Preparación de datos
- Transponer, fusionar columnas, transponer
- Cargar datos en Excel
- 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: 0024 Power Query: combinar filas en una sola celda.zip
Tener acceso
Si bien tener una mesa plana es excelente para la manipulación de datos, es posible que no siempre sea óptimo:
- No es cómo los usuarios quieren ver la información.
- Es posible que sea necesario optimizarlo para fusionarlo con otras fuentes de datos.
Por lo tanto, incluso si tenemos un diseño de datos estructurado, es posible que necesitemos combinar filas.
Guión
Aquí están los datos del archivo de ejemplo:
En la captura de pantalla, tenemos una lista de empleados y cualquier equipo que la empresa les haya asignado. Por ejemplo, podemos ver que Anna Watkins tiene una computadora portátil, una tarjeta de acceso, un teléfono y una impresora doméstica.
El objetivo es combinar toda la información del equipo de cada empleado en una sola celda (como se muestra a continuación).
Todo esto suena simple, ¿no? Antes de Power Query, necesitaríamos usar fórmulas complejas para lograr este resultado. Pero con Power Query, esto se convierte en unas pocas transformaciones simples.
Bien, comencemos.
Cargar datos en Power Query
Seleccione una celda dentro de la tabla de datos y luego haga clic en Datos de tabla/rango .
Se abre la ventana Power Query y muestra los datos de la tabla.
Ahora estamos listos para comenzar las transformaciones.
Inicialmente, combinaremos Equipo y Atributo del equipo en una sola columna, luego combinaremos las filas en una sola fila para cada empleado.
Fusionar columnas
Seleccione las columnas Equipo y Atributo del equipo , luego haga clic en Transformar columnas de combinación
Para nuestro ejemplo, queremos colocar dos puntos y un espacio entre las dos columnas. Entonces, debemos seleccionar –Personalizado– en la lista desplegable Separador y, en el cuadro a continuación, ingresar Dos puntos ( : ) , seguido de un espacio. personaje.
Le he dado a la nueva columna el nombre Equipo ; luego podemos hacer clic en Aceptar .
Las columnas Equipo y Atributo del equipo se combinan en una, como se muestra en la siguiente captura de pantalla.
Agrupar por con suma
Ahora viene la parte emocionante. En nuestra próxima transformación, creamos un error, pero no te preocupes, porque te explicaré por qué ocurre y qué hacemos al respecto.
Seleccione todas las columnas excepto la columna que se combinará. En nuestro ejemplo, debemos seleccionar las columnas Nombre y Apellido .
Haga clic en Transformar grupo por
Se abre el cuadro de diálogo Agrupar por. Los campos a completar son:
- Nuevo nombre de columna: ingrese un nombre para la columna de celdas combinadas (he usado Equipo )
- Operación: seleccione Suma en la lista desplegable
- Columna : seleccione la columna que se reducirá de filas a una sola celda ( Equipo.1 en nuestro ejemplo).
Luego, haga clic en Aceptar .
Equipo es una columna de texto y hemos seleccionado la operación Suma. Esto crea un error, ya que no podemos sumar texto.
Como puede ver arriba, nuestra columna ahora contiene errores.
El menú desplegable de operaciones tiene muchas opciones: Suma, Promedio, Mediana, Mín., Máx., Contar, Contar filas distintas y Todas las filas. Desafortunadamente, ninguna de estas es la opción que queremos.
Pero no te preocupes; Usamos Suma como función de marcador de posición, solucionaremos el error en el siguiente paso.
Editar el código M
A continuación, editamos el código M en la barra de fórmulas para cambiar la función List.Sum por la función Text.Combine .
Con suerte, la barra de fórmulas está visible; de lo contrario, haga clic en Ver barra de fórmulas para habilitarlo.
La barra de fórmulas de Power Query es similar a la de Excel. Sin embargo, las fórmulas deben ser Código M en lugar de fórmulas estándar de Excel.
Estamos utilizando la función Text.Combine para fusionar el texto: https://docs.microsoft.com/en-gb/powerquery-m/text-combine
Text.Combine tiene la siguiente sintaxis:
Text.Combine(texts as list, optional separator as nullable text) as text- Textos: tiene que ser una lista de cadenas de texto, que es lo que tenemos, porque cada individuo tiene una lista de equipos.
- Separador: debe ser la cadena de texto utilizada para separar cada elemento
Entonces, si quisiéramos separar por punto y coma ( ; ) y un carácter de espacio , la fórmula sería:
Text.Combine([Equipment.1], "; ")Esto significa que en la barra de fórmulas cambiamos el código M de este:
= Table.Group(#"Merged Columns", {"First Name", "Last Name"}, {{"Equipment", each List.Sum([Equipment.1]), type text}})A esto:
= Table.Group(#"Merged Columns", {"First Name", "Last Name"}, {{"Equipment", each Text.Combine([Equipment.1], "; "), type text}})Ahora la ventana de vista previa ha reducido el equipo de cada persona a una sola celda. ¡ASOMBROSO!
Cargar datos en Excel
Eso es todo. Hemos terminado. Volvamos a enviar los datos a Excel.
Haga clic en Inicio Cerrar Cargar (menú desplegable) Cerrar y cargar en…
El Editor de Power Query se cierra y la vista vuelve a Excel. En el cuadro de diálogo Importar datos, seleccione cargar una tabla en la celda F1 de la hoja de cálculo existente.
Haga clic en Aceptar para cerrar el cuadro de diálogo Importar datos.
Los datos finales en Excel se ven así:
Power Query combina filas específicas
El segundo escenario ocurre cuando queremos combinar filas específicas. Esto suele ocurrir cuando hay varias filas de encabezado en nuestros datos de origen. Dado que Power Query solo puede tener una única fila de encabezado, debemos combinar el texto de la fila en una sola fila.
Guión
Aquí están los datos del archivo de ejemplo.
En la captura de pantalla anterior, tenemos varias filas de encabezado. Por ejemplo, podemos ver que Nombre y Nombre se utilizan para describir las celdas de la columna A.
El objetivo es concatenar el texto en una sola fila (como se muestra a continuación).
Bien, intentémoslo.
Convertir los datos en una tabla.
Nuestros datos no están actualmente en formato de tabla. Seleccione cualquier celda de los datos y haga clic en Insertar tabla (o presione Ctrl + T).
En el cuadro de diálogo Crear tabla , asegúrese de que esté incluido todo el rango de datos y luego haga clic en Aceptar .
A continuación, seleccione una celda en la tabla. Luego, haga clic en Nombre de la tabla de diseño de tabla y proporcione a la tabla un nombre significativo. Para este ejemplo, elegí tblEmployeeEquipment .
NOTA: Si no desea convertir la información de origen en una tabla, las alternativas son: (1) crear un rango con nombre que incluya los datos , (2) conectarse a los datos de otro libro de trabajo .
Cargue los datos en Power Query
Seleccione una celda dentro de la tabla, luego haga clic en Datos de tabla/rango . Se abre la ventana Power Query y muestra una vista previa de los datos.
Ahora estamos listos para comenzar las transformaciones.
Preparación de datos
Dependiendo de su configuración predeterminada, el cuadro Pasos aplicados puede incluir un paso llamado Tipo modificado ; Si es así, elimine este paso. No lo necesitamos.
Debido a que nuestros datos provienen de una tabla, la fila del encabezado se usa automáticamente como fila de encabezado en Power Query. No queremos esto, así que haga clic en Transformar Usar la primera fila como encabezados (menú desplegable) Usar encabezados como primera fila
Transponer, fusionar columnas, transponer
No hay ninguna transformación de filas de combinación en Power Query. Sin embargo, si transponemos datos, las filas se convierten en columnas. Lo que significa que podemos usar la transformación fusionar columnas en su lugar.
Haga clic en Transformar Transponer .
Seleccione Columna1 y Columna2 y luego haga clic en Transformar Fusionar columnas .
En el cuadro de diálogo Fusionar columnas, ingrese lo siguiente:
- Separador: Espacio
- Nuevo nombre de columna: fila de encabezado
La ventana de vista previa debería mostrar lo siguiente:
La columna Equipo solo tenía una fila de encabezado; por lo tanto, el texto se ha combinado con las palabras Columna1 para dar un valor de Equipo de Columna1 . Limpiemos eso.
Con la columna Fila de encabezado seleccionada, haga clic en Transformar Reemplazar valores . Reemplace " Columna1 " con un espacio en blanco.
NOTA: Asegúrese de que la Columna 1 tenga un carácter de espacio después.
Transponga los datos nuevamente haciendo clic en Transformar Transponer .
A continuación, promueva los encabezados haciendo clic en Transformar Usar la primera fila como encabezados .
La ventana de vista previa debería verse así. ¡PERFECTO!
Cargar datos en Excel
Hemos terminado. Ahora podemos cargar los datos en Excel. Haga clic en Inicio Cerrar Cargar (menú desplegable) Cerrar y cargar en…
Usando el cuadro de diálogo Importar datos, cargue una tabla en la celda F1 de la hoja de trabajo existente.
Los datos finales en Excel se ven así:
Conclusión
Lo que habrían sido escenarios complejos para Excel se han simplificado con Power Query. Sin embargo, combinar filas en Power Query requiere una mentalidad ligeramente diferente. Por lo tanto, necesitamos comprender las transformaciones disponibles y cómo podemos unirnos a ellas para lograr los resultados deseados.
Artículos Relacionados:
- Cómo dividir celdas en Excel
- Power Query: divide celdas delimitadas en filas
- Power Query: búsqueda de valores mediante combinación
Deja una respuesta