Forum Discussion
Replace values in a given column (column is comma separated id's) with lookupvalue from a query
Imke Feldmann - I wonder if you have a suggestion :) ?
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
- 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
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"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 RemoveUnusedcan 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 adviseSergeiBaklan
