Cómo convertir datos de múltiples columnas y subcolumnas combinadas en filas usando Power Query

Hola a todos,

Recientemente comencé a explorar powerBI y me fascinaron las funciones de consulta de energía.

Logré usar la función de no pivotar para transformar mis datos de columnas a filas. Mientras trabajo con varias columnas y subcolumnas combinadas, no puedo obtener los resultados deseados. He estado tratando de transformar estos datos:

Datos PTM (celda B a E) Datos SMC (celda F a I)

Nombre de escuela	Números de PTM	Masculino	Mujer	Total	Números de SMC	Masculino	Mujer	Total
Escuela 1	1	3	5	8	1	2	3	5
escuela 2	1	2	3	5	1	2	3	5
Escuela 3	5	dieciséis	33	49	3	10	19	29

(No se pueden copiar las columnas combinadas arriba, por lo tanto, los encabezados mencionados en el texto)

A esto:

Nombre de escuela	Evento	Numero de evento	Masculino	Mujer	Total
Escuela 1	PTM	1	3	5	8
escuela 2	PTM	1	2	3	5
Escuela 3	PTM	5	dieciséis	33	49
Escuela 1	SMC	1	2	3	5
escuela 2	SMC	1	2	3	5
Escuela 3	SMC	3	10	19	29

¿Se puede hacer esto usando unpivot o cualquier otra función en PowerBI?

Saludos,

sreejit

Resultado:

Lo que usé para el código fuente:

PTM y SCM se fusionan + celdas centradas

Código de consulta M y pasos realizados:

Explicación:
Después de cargar los datos, asegúrese de que no se promocionaron encabezados.

Su primer paso es transponer la tabla en la pestaña ‘Transformar’. Esto reducirá nuestros tipos de eventos PTM y SCM.

Desde allí, seleccionamos Rellenar –> Rellenar hacia abajo en la pestaña ‘Transformar’. Esto completará los valores nulos que dejan las celdas de Excel combinadas.

Luego de eso hacemos ctrl + seleccionamos ambos [Column1] también conocido como tipo de evento y [Column2] también conocido como Género, luego vaya a la pestaña ‘Agregar columna’ y seleccione Combinar columnas. Elijo no agregar un separador, pero puedes usar lo que sea, esto entrará en juego más adelante.

Después de fusionar, transponga la tabla nuevamente.

Felicidades, Headers, estás siendo ascendido.

Desde allí, ctrl + seleccione todas las columnas excepto [School Name] y [Event #]. Vuelva a la pestaña ‘Transformar’ y seleccione Dejar de girar columnas –> Dejar de girar solo columnas seleccionadas.

Ahora estamos llegando a alguna parte. Selecciona el [Attribute] también conocida como columna combinada y elija Dividir columna -> Dividir por posición en la pestaña ‘Transformar’. Deberá dividir por delimitador si usó uno durante el paso de la columna de fusión. Simplemente divida por cualquier delimitador que elija. Simplemente me gusta dividir por posición.

Finalmente, seleccione [Attribute.2] columna y seleccione Columna dinámica en la pestaña ‘Transformar’. Agregue toques finales como nombres de encabezado y cambios de tipo.

Si bien este método es posible, es un poco engorroso y se ejecutará mucho más lento en conjuntos de datos más grandes. Personalmente, intentaría reestructurar los datos que obtiene u organizarlos de manera diferente. Por ejemplo, me desharía de la columna total y dejaría que DAX se encargara de ella en el back-end. Me gusta tener mis datos organizados de la siguiente manera, ya que creo que le resultará más fácil manipularlos con DAX:

Un agradecimiento especial al siguiente sitio web por su orientación, ¡fue un problema divertido de resolver!
https://www.newtechdojo.com/unpivot-columns-in-power-bi/#dualrow

todos los pasos ejecutados desde la GUI:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRCgjxxUoGO2MjY3WilYKTM/LzcxT8EnNTgaJ++cV6CvlpChBdvok5IEG31FwIIyS/JDEHSVWwrzNOVUhmGwIFQdgYiE2B2ALKN4KLISk2wpDEoxiuwAwkC+KZWELVGRqACBDPyFIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Merged Columns" = Table.CombineColumns(#"Promoted Headers",{"PTM", "PTM_1", "PTM_2", "PTM_3"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"PTM"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"SCM", "SCM_4", "SCM_5", "SCM_6"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"SCM"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Merged Columns1", {""}, "Attribute", "Value"),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Unpivoted Other Columns", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"School Name", type text}, {"PTM", type text}, {"Nos. of PTM:Male:Female:Total", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Nos. of PTM:Male:Female:Total", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Nos. of PTM:Male:Female:Total.1", "Nos. of PTM:Male:Female:Total.2", "Nos. of PTM:Male:Female:Total.3", "Nos. of PTM:Male:Female:Total.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Nos. of PTM:Male:Female:Total.1", type text}, {"Nos. of PTM:Male:Female:Total.2", type text}, {"Nos. of PTM:Male:Female:Total.3", type text}, {"Nos. of PTM:Male:Female:Total.4", type text}}),
    #"Promoted Headers2" = Table.PromoteHeaders(#"Changed Type2", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers2",{{"School Name", type text}, {"SCM", type text}, {"Nos. of SMC", Int64.Type}, {"Male", Int64.Type}, {"Female", Int64.Type}, {"Total", Int64.Type}})
in
    #"Changed Type3"