Lookup and Concatenate

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.