Replace values in a given column (column is comma separated id's) with lookupvalue from a query

[Ian Bruckner]

How can I replace the values in this first column

 

With the lookup value from this other query? Ideally it comma separates the values, skipping anything that's null in the lookup (so no blank spaces between commas.

[#2]

@Imke Feldmann - I wonder if you have a suggestion :) ?

[#3]

Hi Ian,

If I understood you correctly the transformation is like this

If so, the script could be

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextsToLists = Table.AddColumn(Source, "Custom", each Text.SplitAny([col],",")),
    AddIndex = Table.AddIndexColumn(TextsToLists, "Index", 0, 1),
    ListsToRows = Table.ExpandListColumn(AddIndex, "Custom"),
    IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
    MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},Table2,{"ID"},"Table2",JoinKind.LeftOuter),
    GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "Table2", {"Value"}, {"Value"}),
    GroupByIndex = Table.Group(GetValues, {"Index"}, {{"Count", each _, type table}}),
    ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(GroupByIndex[Count]{[Index]}[Value],",")),
    RemoveUnused = Table.SelectColumns(ValuesToCSV,{"Custom"})
in
    RemoveUnused

Please see attached

[#4]

That was very helpful, thank you. I was having just a few more issues with it as-is. Mainly, I needed to adjust the ValuesToCSV line. Before I did so, it wasn't putting the right replaced values in the rows, and error-ring out on many.

ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(Table.ToList(Table.RemoveColumns([Count], "ID")),",")),

So the full thing looks like:

 let
Source = #"Work Trackr",
RemovedOtherColumns = Table.SelectColumns(Source,{"ID", "Team Members"}),
TextsToLists = Table.AddColumn(RemovedOtherColumns,"Custom", each Text.SplitAny([Team Members],",")),
ListsToRows = Table.ExpandListColumn(TextsToLists, "Custom"),
IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},UserInfo,{"user"},"UserInfo",JoinKind.LeftOuter),
GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "UserInfo", {"email"}, {"email"}),
#"Filtered Rows" = Table.SelectRows(GetValues, each ([email] <> "")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom", "Team Members"}),
GroupByIndex = Table.Group(#"Removed Columns", {"ID"}, {{"Count", each _, type table}}),
ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(Table.ToList(Table.RemoveColumns([Count], "ID")),",")),
#"Removed Columns1" = Table.RemoveColumns(ValuesToCSV,{"Count"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "AT Team Members"}})
in
#"Renamed Columns"

[#5]

Ian,

I'd use Index to avoid possible errors in enumeration and extract list to combine from GroupByIndex[Count] column. You don't need filter [email] then. Like this and attached

let
    Source = Excel.CurrentWorkbook(){[Name="WorkTracker"]}[Content],
    RemoveOtherColumns = Table.SelectColumns(Source,{"ID", "Team Members"}),
    AddIndex = Table.AddIndexColumn(RemoveOtherColumns, "Index", 0, 1),
    TextsToLists = Table.AddColumn(AddIndex,"Custom", each Text.SplitAny([Team Members],",")),
    ListsToRows = Table.ExpandListColumn(TextsToLists, "Custom"),
    IDasNumber = Table.TransformColumnTypes(ListsToRows,{{"Custom", Int64.Type}}),
    MergeWithLookupTable = Table.NestedJoin(IDasNumber,{"Custom"},
        UserInfo,{"user"},"UserInfo",JoinKind.LeftOuter),
    GetValues = Table.ExpandTableColumn(MergeWithLookupTable, "UserInfo", {"email"}, {"email"}),
    GroupByIndex = Table.Group(GetValues, {"Index"}, {{"Count", each _, type table}}),
    ValuesToCSV = Table.AddColumn(GroupByIndex, "AT Team Members",
        each Text.Combine(GroupByIndex[Count]{[Index]}[email],",")),
    ExtractID = Table.AddColumn(ValuesToCSV, "ID", each [Count][ID]{0}),
    RemoveUnused = Table.SelectColumns(ExtractID,{"ID", "AT Team Members"})
in
    RemoveUnused

[#6]

can you please tell where to write this script. I have the same exact requirement. Trying to understand what script is this and how to execute? Please advise

[#7]

can you please tell where to write this script. I have the same exact requirement. Trying to understand what script is this and how to execute? Please advise@Sergei Baklan 

[#8]

Please ignore, I found it is Power Query and it all worked.

Thanks for the solution