Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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  ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    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.

     

vicky_'s avatar
vicky_
Icon for Super User rankSuper User
2 years ago

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], ", ")