Forum Discussion
Group multiple columns to one row per ID
Hi all,
I would like to get your help in issue that I'm faced with.
I have this table:
I would like to group all these colums to one row per Computer ID (please find the desired result below):
Is it possible to perform? what should I do in order to get this?
Thanks in advance!
Hi, Chen2023 ;
Try it.
1.unpivot [February],[March],[April] columns:
2.select the [Attribute] column the pivot it.
The final show:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMLsksSwUy/EpzcmBUrA5EEioWnJOaWpCZl45DGko5FaUmZqfkl+eB5Y3wmW2EEENVgyaJqgYkaYzFOUh6jfEZbILiSEzNJjg1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Computer ID" = _t, February = _t, March = _t, April = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Computer ID", Int64.Type}, {"February", type text}, {"March", type text}, {"April", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Null",null,Replacer.ReplaceValue,{"February", "March", "April"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Computer ID"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Chen2023 ;
Try it.
1.unpivot [February],[March],[April] columns:
2.select the [Attribute] column the pivot it.
The final show:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJMLsksSwUy/EpzcmBUrA5EEioWnJOaWpCZl45DGko5FaUmZqfkl+eB5Y3wmW2EEENVgyaJqgYkaYzFOUh6jfEZbILiSEzNJjg1xwIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Computer ID" = _t, February = _t, March = _t, April = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Computer ID", Int64.Type}, {"February", type text}, {"March", type text}, {"April", type text}}), #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Null",null,Replacer.ReplaceValue,{"February", "March", "April"}), #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Replaced Value", {"Computer ID"}, "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Columns", List.Distinct(#"Unpivoted Columns"[Attribute]), "Attribute", "Value") in #"Pivoted Column"
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Chen2023 Do a Group By step by ID and use a Maximum aggregator for each of your columns. You need to use the Advanced options of Group by.
