Forum Discussion
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...
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,
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,