How to combine rows with condition

Hi,

This M code will get you from image1 to image2

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1szAyUNJRckktSS3KzcxLBbKhKFYHh7xffomCY2lJfm5iSWoKIcVgbnFmWh4hVUBkhFBgSIqd6Iqx24lFFcTOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, name = _t, #"Automstion Status" = _t, State = _t, Priority = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"name", type text}, {"Automstion Status", type text}, {"State", type text}, {"Priority", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Automation statu", each List.Max([Automstion Status]), type nullable text}, {"State", each List.Max([State]), type nullable text}, {"Priority", each List.Sum([Priority]), type nullable number}})
in
    #"Grouped Rows"

Hope this helps.

Hi Changlin 

You can refer to the following example.

1.Put the following code to Advanced Editor in Power Query

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1szAyUNJRckktSS3KzcxLBbKhKFYHh7xffomCY2lJfm5iSWoKIcVgbnFmWh4hVUBkhFBgSIqd6Iqx24lFFcTOWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, name = _t, #"Automstion Status" = _t, State = _t, Priority = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"name", type text}, {"Automstion Status", type text}, {"State", type text}, {"Priority", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID"}, {{"Automation statu", each List.Max([Automstion Status]), type nullable text}, {"State", each List.Max([State]), type nullable text}, {"Priority", each List.Sum([Priority]), type nullable number}})
in
    #"Grouped Rows"

 

 

 

2.You can create a new calculated table

 

 

Table 2 = SUMMARIZE('Table (2)',[ID],"Automation status",MAX('Table (2)'[Automstion Status]),"State",MAX('Table (2)'[State]),"Priority",SUM('Table (2)'[Priority]))

 

 

 

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.