How to combine rows with condition

[Changlin]

Hi,

I have a table shown as below:

I want to combine rows with the same ID and keep the values in the "Automation status", "State" and "Priority".          

ID	Automation status	State	Priority
456820	Non-Automated	Design	2
456821	Non-Automated	Design	2

How to implement this goal?

Thanks,

Richard

[Ashish_Mathur · Accepted solution]

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.

[v-xinruzhu-msft · Accepted solution]

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.

[Ashish_Mathur · Accepted solution]

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.

[SivaMani]

@Changlin, You can try the below options,

Power Query - https://learn.microsoft.com/en-us/power-query/group-by

DAX - https://learn.microsoft.com/en-us/dax/summarize-function-dax