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

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