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
Hi jlauyfc1 ,
How about this:
Here the code in Power Query M that you can paste into the advanced editor (if you do not know, how to exactly do this, please check out this quick walkthrough)
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUYpQitWJVkqCs5KBrEgwKwXOSoWwYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"A", type text}, {"B", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"A", Order.Ascending}}),
#"Grouped Rows" = Table.Group(#"Sorted Rows", {"B"}, {{"Grouping", each _, type table [A=nullable text, B=nullable text]}}),
#"Added Index" = Table.AddColumn(#"Grouped Rows", "Index", each Table.AddIndexColumn ( [Grouping], "Index", 1 )),
#"Removed Columns 1" = Table.RemoveColumns(#"Added Index",{"B", "Grouping"}),
#"Expanded Index" = Table.ExpandTableColumn(#"Removed Columns 1", "Index", {"A", "B", "Index"}, {"A", "B","Index"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Index", List.Distinct(#"Expanded Index"[B]), "B", "A"),
#"Removed Columns 2" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns 2"
Let me know, if this solved your query.
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/