Forum Discussion
Lookup and Concatenate
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
Anonymous
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
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.
4 Replies
vicky_Super User
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], ", ")
ryan_mayuAnonymous
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
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.