show different combinations for unique IDs

[alai9]

Hello,

Is it possible to convert this table in PowerQuery:

ID	Type
1000	A
1000	B
1000	C
1001	C
1002	C

to this view:

ID	Type
1000	A B C
1001	C
1002	C

NOTE: there can be more or less than 3 values for Types (depends on user input).

Thanks in Advance!

[edhans · Accepted solution]

Yes @alai9 - see this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUXJUitWBc5yQOc4wjiEyxwjCiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"AllRows", each _, type table [ID=nullable text, Type=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][Type], " ")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"})
in
    #"Removed Other Columns"

It turns this:

into this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[v-alq-msft · Accepted solution]

Hi, @alai9 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may add a new step with following m codes.

= Table.Group(#"Changed Type", {"ID"}, {{"Type", each Text.Combine(_[Type]," "),type text}})

Result:

Best Regards

Allan

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

[edhans · Accepted solution]

Yes @alai9 - see this code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSUXJUitWBc5yQOc4wjiEyxwjCiQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Type = _t]),
    #"Grouped Rows" = Table.Group(Source, {"ID"}, {{"AllRows", each _, type table [ID=nullable text, Type=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine([AllRows][Type], " ")),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"ID", "Custom"})
in
    #"Removed Other Columns"

It turns this:

into this:

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.