Search code examples
excelpowerbipowerquerypowerbi-desktopm

PowerQuery - Replace list values with join and combine without expanding

What I have:

Main query has a field with [List] Object. each entry will have 1-5 entries like this:

Main Table

Name Stamps
John [List]
Tim [List]

John's List:

GUID1
GUID2

Tim's List

GUID1
GUID3
GUID6

GUID|Mapping Table

GUID Name
GUID1 Stamp1
GUID2 Stamp2
...

Desired output

Name Stamp Name(s)
John Stamp1, Stamp2
Tim Stamp1,Stamp3,Stamp6

I know I can do this with Expand\Groupby, etc but I am trying to learn to work more with the lists. I can use Text.Combine to merge the GUIDs or I can expand and then join the list to the GUID table to get the name. I can't seem to get the order right in a function to this without expanding.

I'm thinking something like this List.Accumulate(tags[name], null, (s,c) => Text.Combine(tags[name]{List.PositionOf(tags[alias], s)}, ", ") but that just errors out. Any help?

Solution

  • enter image description here

    let
    Source = #table( {"Name", "Stamps"}, { { "John" , {"GUID1", "GUID2"}}, { "Tim" ,{"GUID1", "GUID2", "GUID6"}} } ),
    Get = Table.TransformColumns(Source,{{"Stamps", (x)=>  
let a = List.Transform(x, (y)=> GUIDs[Name]{ List.PositionOf(GUIDs[GUID],y)}  ),
b = Text.Combine( a, ", ")
in b
, type text}})
in
    Get