Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi all,
I have a table with the form:
Header | Value |
a | v1 |
a | v2 |
a | v3 |
b | u1 |
b | u2 |
b | u3 |
c | x1 |
c | x2 |
c | x3 |
and I would like to transform it to:
a | b | c |
v1 | u1 | x1 |
v2 | u2 | x2 |
v3 | u3 | x3 |
How can I do this?
Thank you,
Matteo
Solved! Go to Solution.
Hi. I don't think this is a pivot it won't let you do it. You can try this code that will group by list, expand and transpose. That way you get the result.
#"Filas agrupadas" = Table.Group(#"Last Step", {"Header"}, {{"Recuento", each [Value], type list}}),
#"Valores extraídos" = Table.TransformColumns(#"Filas agrupadas", {"Recuento", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Dividir columna por delimitador" = Table.SplitColumn(#"Valores extraídos", "Recuento", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Recuento.1", "Recuento.2", "Recuento.3"}),
#"Tabla transpuesta" = Table.Transpose(#"Dividir columna por delimitador"),
#"Encabezados promovidos1" = Table.PromoteHeaders(#"Tabla transpuesta", [PromoteAllScalars=true])
First group by head, then expand them in a single row split by commas. Create columns for each comma and finally transpose.
Hope this helps I have tried it with the table you send as example and it worked.
Regards,
Happy to help!
@Anonymous , Refer if this can help
https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/
Hi. I don't think this is a pivot it won't let you do it. You can try this code that will group by list, expand and transpose. That way you get the result.
#"Filas agrupadas" = Table.Group(#"Last Step", {"Header"}, {{"Recuento", each [Value], type list}}),
#"Valores extraídos" = Table.TransformColumns(#"Filas agrupadas", {"Recuento", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
#"Dividir columna por delimitador" = Table.SplitColumn(#"Valores extraídos", "Recuento", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Recuento.1", "Recuento.2", "Recuento.3"}),
#"Tabla transpuesta" = Table.Transpose(#"Dividir columna por delimitador"),
#"Encabezados promovidos1" = Table.PromoteHeaders(#"Tabla transpuesta", [PromoteAllScalars=true])
First group by head, then expand them in a single row split by commas. Create columns for each comma and finally transpose.
Hope this helps I have tried it with the table you send as example and it worked.
Regards,
Happy to help!
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
102 | |
68 | |
45 | |
37 | |
37 |