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

Question

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.

sergeibaklan · Answer

Hi Ian,
&nbsp;
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

ian bruckner · Answer

Imke Feldmann&nbsp;- I wonder if you have a suggestion :) ?

ian bruckner · Answer

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.&nbsp;ValuesToCSV = Table.AddColumn(GroupByIndex, "Custom", each Text.Combine(Table.ToList(Table.RemoveColumns([Count], "ID")),",")),&nbsp;So the full thing looks like:&nbsp;letSource = #"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] &lt;&gt; "")),#"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"

sergeibaklan · Answer

Ian,
&nbsp;
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
&nbsp;

kalyanpolasi · Answer

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

Forum Discussion

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

7 Replies

Resources