Group multiple columns to one row per ID

[Chen2023]

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!

[v-yalanwu-msft · Accepted 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.

[Greg_Deckler]

@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.