Forum Discussion
Solved
Table.Pivot - without aggreagation but to list values
I am new to power query, how can I pivot a table without aggreagation but showing a list of (distinct) value? For example: Table: A | B ----- a X b X c Y d Y e Y Expected res...
use this formula
let Query1 = Table.FromColumns({{"a".."e"},{"X","X","Y","Y","Y"}},{"A","B"}), Pivot = Table.Pivot(Query1, List.Distinct(Query1[B]), "B", "A", each _), Custom1 = Table.FromRows(List.Zip({Pivot[X]{0},Pivot[Y]{0}}),Table.ColumnNames(Pivot)) in Custom1it result in
use this formula
let
Query1 = Table.FromColumns({{"a".."e"},{"X","X","Y","Y","Y"}},{"A","B"}),
Pivot = Table.Pivot(Query1, List.Distinct(Query1[B]), "B", "A", each _),
Custom1 = Table.FromRows(List.Zip({Pivot[X]{0},Pivot[Y]{0}}),Table.ColumnNames(Pivot))
in
Custom1
it result in
This is nicer with shorter coding. I amended your last line of code to make it work on any number of columns:
= Table.FromRows(List.Zip(Record.ToList(Table.First(Pivot))), Table.ColumnNames(Pivot))
Yes, it is dynamic and can work on any number, if you need more help or explanation just let me know