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: 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...
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,