Group a column based on two ids

[Anonymous]

Hello to all

I have a table that is like this:

id      fieldid      issueid    label
1         1               1            a

2         2               1            b

3         1               1            c

4         3               2            d

5         2               3            e

6         1               4            f

and I need to get to this:

id      fieldid      issueid    label
1         1               1            a c

2         2               1            b

4         1               2            d

5         2               2            e

6         1               3            f

I need to join labels both by fieldid and issueid maintaining the rest the same.

I manage to do a merge by issueid without man problems but i mix the fieldid...

Can this be done? (I'm using M)

Thanks

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Hi,

In your screenshot, the [filedid] and [issueid] in your expected table is different from your raw table I guess it should return the  [filedid] and [issueid] same as raw table like below, right?

id     fieldid     issueid     label
1          1              1            a c
2          2              1              b
4          3              2              d
5          2              3              e
6          1              4              f

Based on my test,you can utilize Table.Group function like below:
Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})

You can open the “Advanced Editor” and enter the code:

let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSgeNEpVidaCUjIMsIKpIEFjFGUpMMFjEBsoyh6lLAIqZQHkg0FSxiBtUBUpumFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [id = _t, fieldid = _t, issueid = _t, label = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", Int64.Type}, {"fieldid", Int64.Type}, {"issueid", Int64.Type}, {"label", type text}}),

    #"Grouped Rows" = Table.Group(#"Changed Type", {"fieldid", "issueid"}, {{"Count", each Text.Combine([label],",")}, {"minid", each List.Min([id]), type number}})

in

    #"Grouped Rows"

Click the “done” and you can see the result.

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/kf06vvjdcjwuhhl/Group%20a%20column%20based%20on%20two%20ids2.pbix?dl=0

Regards,

Daniel He

[Anonymous]

@v-danhe-msft

You nail it.

Thanks.