Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
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 |
---|---|
72 | |
68 | |
67 | |
41 | |
39 |
User | Count |
---|---|
48 | |
44 | |
29 | |
28 | |
28 |