Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.