Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

Power Query: Separar por filas múltiples columnas

Recientemente un cliente me solicitaba la generación de una macro (en VBA para Excel) que transformara un listado de registros con ciertos campos concatenados en un listado de elementos únicos...
Tal y como se ve en la imagen siguiente:
Power Query: Separar por filas múltiples columnas

La idea, como se observa en la imagen anterior, es separar en varias filas cada registro, de acuerdo al número de elementos concatenados en diferentes Columnas... por ejemplo:
24/05/2020 ES;DE;FR 267 1544;1020;1915
está compuesto de tres registros, con valores correspondientes entre los tres paises y los tres importes de ventas:
ES - 1544
DE - 1020
FR - 1912
El resto de campos debería repetirse en cada columna final mostrada...
Adicionalmente pretendemos hacer un reparto equitativo de las unidades para cada fila generada!!.

Obviamente desarrollé la programación.. pero me quedé pensando si era posible evitar la programación y generar una consulta en Power Query que realizara el mismo proceso... y aquí está ;-)
Veamos como separar múltiples columnas por filas.

Partimos de nuestra fuente de datos (una tabla llamada 'TblDatos'), la cual cargaremos como siempre desde la ficha Datos > grupo Obtener y transformar > botón Desde Tabla o rango.
Una vez en el editor de consultas Agregaremos una columna personalizada nueva con la siguiente fórmula:
= [Unidades vendidas]/List.Count(Text.Split([Paises],";"))
Power Query: Separar por filas múltiples columnas

Con esta fórmula conseguimos el reparto por unidades entre cada fila futura a generar...
Clave para este cálculo la función M:
Text.Split(text as text, separator as text) as list
que generar una lista a partir de una cadena de texto...
Por ejemplo, de la cadena de texto: ES;DE;FR generará una lista {ES DE FR}.

Sobre esta lista (que volveremos a utilizar posteriormente) aplicamos otra función M:
List.Count(list as list) as number
que nos devolverá el número de elementos en dicha lista... 3 en mi ejemplo anterior.
Finalemente forzamos el cociente, esto es, el reparto de 'Unidades vendidas' entre ese conteo de elementos...

Power Query: Separar por filas múltiples columnas
Con el cálculo de unidades repartidas pasamos al siguiente paso...

Con la función M:
Table.FromColumns(lists as list, optional columns as any) as table
crearemos una tabla para cada registro compuesta por el desglose de cada columna.
ES - 1544
DE - 1020
FR - 1912
Power Query: Separar por filas múltiples columnas

Tabla que posteriormente expandiremos...
Para incorporar nuestra fórmula Agregaremos una columna personalizada nueva con la fórmula:
= Table.FromColumns({Text.Split([Paises],";"),Text.Split([Ventas],";")},{"Pais","Ventas"})
Power Query: Separar por filas múltiples columnas

Table.FromColumns genera una tabla de dos columnas (llamadas 'Pais' y 'Ventas') a partir de las listas obtenidas con Text.Split de los campos originales [Paises] y [Ventas], indicado en el primer argumento:
{Text.Split([Paises],";"),Text.Split([Ventas],";")}

Con la 'tabla' creada para cada fila, eliminamos las columnas originales [Paises] y [Ventas] que ya no necesitamos.

Finalmente expandimos la última columna personalizada y extraemos las dos nuevas columnas..
Power Query: Separar por filas múltiples columnas


Nuestros datos ya aparecen como pretendíamos, separados por filas..


Solo quedan un par de pasos simples para acabar con nuestra consulta...
Reordenar la posición de las columnas, y
Cambiar el tipo de dato de la nueva columna de Ventas a type.number
Listos para cerrar y cargar en nuestra hoja de cálculo!!

El código completo, que podemos ver en el editor avanzado sería...
let
Origen = Excel.CurrentWorkbook(){[Name="TblDATOS"]}[Content],
Tipo_Cambiado = Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"Paises", type text}, {"Ventas", type text}, {"Unidades vendidas", Int64.Type}}),
AddCol_RepartoUDS = Table.AddColumn(Tipo_Cambiado, "RepartoUds", each [Unidades vendidas]/List.Count(Text.Split([Paises],";"))),
AddCol_CreaTabla = Table.AddColumn(AddCol_RepartoUDS, "NuevasCols", each Table.FromColumns({Text.Split([Paises],";"),Text.Split([Ventas],";")},{"Pais","Ventas"})),
ColumnasQuitadas1 = Table.RemoveColumns(AddCol_CreaTabla,{"Unidades vendidas", "Ventas", "Paises"}),
Expande_NuevasCols = Table.ExpandTableColumn(ColumnasQuitadas1, "NuevasCols", {"Pais", "Ventas"}, {"Pais", "Ventas"}),
Columnas_Reordenadas = Table.ReorderColumns(Expande_NuevasCols,{"Fecha", "Pais", "RepartoUds", "Ventas"}),
Tipo_Cambiado1 = Table.TransformColumnTypes(Columnas_Reordenadas,{{"Ventas", type number}})
in
Tipo_Cambiado1

Power Query: Separar por filas múltiples columnas


This post first appeared on EXCEL FORO: EJERCICIOS, EJEMPLOS, SOLUCIONES, DUDA, please read the originial post: here

Share the post

Power Query: Separar por filas múltiples columnas

×

Subscribe to Excel Foro: Ejercicios, Ejemplos, Soluciones, Duda

Get updates delivered right to your inbox!

Thank you for your subscription

×