Need idea in Group by

[Baskar]

Hi all , 

Please help me on this case , I fight with this almost last 5 hours 🙂

Orginal Table look like this :

ID          Name

1            Pen

2            Chair

2            Table

3            Plug

3            Power

3            shocket

4           Board

4            Marker

4           Eraser

4           Clip

This data need to be tranlate to 

ID            New Column

1             Pen

2             Chair, table 

3             Plug, Power, shocket

4              Board, Marker, Eraser, Clip

[Greg_Deckler · Accepted solution]

Here is a DAX solution:

My queries

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpIzVOK1YlWMgKynTMSM4vgvJDEpJxUMM8YpC6nNB3ByS9PLYLzijPyk7NTS8B8EyDfKT+xKAXO800syoYqBnFdixKLkbjOOZkFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
    #"Changed Type"

I used Enter Data queries, you could just use the same source, get rid of all columns except ID and then only get the distinct values from ID.

Relate your two tables on ID.

In IDs table, create the following column:

Names = CONCATENATEX(CALCULATETABLE(RELATEDTABLE('Grouping')),'Grouping'[Name],",")

[Baskar]

++ @ankitpatira  @Greg_Deckler @ImkeF @MattAllington @v-haibl-msft

[Greg_Deckler · Accepted solution]

Here is a DAX solution:

My queries

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpIzVOK1YlWMgKynTMSM4vgvJDEpJxUMM8YpC6nNB3ByS9PLYLzijPyk7NTS8B8EyDfKT+xKAXO800syoYqBnFdixKLkbjOOZkFSrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}})
in
    #"Changed Type"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}})
in
    #"Changed Type"

I used Enter Data queries, you could just use the same source, get rid of all columns except ID and then only get the distinct values from ID.

Relate your two tables on ID.

In IDs table, create the following column:

Names = CONCATENATEX(CALCULATETABLE(RELATEDTABLE('Grouping')),'Grouping'[Name],",")

[Baskar]

Thanks @Greg_Deckler.

[Greg_Deckler · Accepted solution]

No problem! Learned a new DAX function, CONCATENATEX. Nifty!