Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

Need Help
  • ibarrau's avatar
    ibarrau
    6 years ago

    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, 

2 Replies

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    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,