Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
Anonymous
Not applicable

Pivot table without aggregation

Hi all,

I have a table with the form:

HeaderValue
av1
av2
av3
bu1
bu2
bu3
cx1
cx2
cx3

 

and I would like to transform it to:

abc
v1u1x1
v2u2x2
v3u3x3

 

How can I do this?

 

Thank you,

Matteo

1 ACCEPTED SOLUTION
ibarrau
Super User
Super User

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, 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@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/

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
ibarrau
Super User
Super User

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, 


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors