Lookup and Concatenate

[thecoolcracker]

Hello, I'm new to Power BI and looking for a solution for my scenario below:

I have a SharePoint field that has multiple values in one column. This is in the form of an ID. I call this Table A

User_ID

12, 96, 42

1

12, 22

I have an user information list that corresponds to each of these IDs: I call this Table B

ID	Name
1	Tom
12	John
15	David
22	Julie
42	Heather
96	Adam
100	Stacy

I'm looking for an output on a column in Table A like the below:

ID	Name
12, 96, 42	John; Adam; Heather
1	Tom
12, 22	John; Julie

I tried different options, but couldn't succeed. I have several columns on Table A that I need to accomplish this. 

Please help

Jimbob

[ryan_mayu · Accepted solution]

@thecoolcracker 

@vicky_ 's solution is great. Just add another solution for the last step.

you can group by in the PQ

= Table.Group(#"Expanded Table B", {"Index"}, {{"id", each Text.Combine([User_ID],","), type nullable text}, {"name", each Text.Combine([Name],","), type nullable text}})

then delete the index column

pls see the attachment below

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"User_ID", type text}}),
    Custom1 = Table.ToRows(Table2),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.ReplaceMatchingItems(Text.Split([User_ID],", "),Custom1),", ")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"User_ID"})
in
    #"Removed Columns"

Hope this helps.

[thecoolcracker]

Thank you for the prompt response. I will try both options.

[ryan_mayu · Accepted solution]

@thecoolcracker 

@vicky_ 's solution is great. Just add another solution for the last step.

you can group by in the PQ

= Table.Group(#"Expanded Table B", {"Index"}, {{"id", each Text.Combine([User_ID],","), type nullable text}, {"name", each Text.Combine([Name],","), type nullable text}})

then delete the index column

pls see the attachment below

[vicky_]

I would recommend that you fix the data in the IDs table in PowerQuery first.

First, I add a column for the group (so that i know which UserIds are grouped together later) - which can be done by going to Add Column > Index Column.

Then I split the UserIds in Transform > Split Column > by Delimiter

Lastly, I select all the newly created userId columns and then go to Transform > Unpivot Columns (I can also delete the attribute column since I don't need that). The result is the below table

After loading back in the data, create a relationship (with both cross-filter direction) between the two UserID columns. Then I can then use the following DAX in a measure to get your output:

Names (concatenated) = CONCATENATEX(Names, Names[Name], ", ")