Forum Discussion
Substring and Lookup
I have 2 tables - one with thousands of data records and one is basically a lookup table. The data table has a column with comma-separated values not always in the same order. For example
a1,b1,c1
b2,c2,a2,
b3, a3
b4
I have lookup values for b1,b2... in the lookup table.
b1 A
b2 B
b3 C
I need a custom column with lookup values of b1, b2, b3... in the data table. I have tried list lookup, text.* list.* formulas and nothing seems to be working. Can anyone suggest some ideas?
You can split the comma-separated column into new rows, merge on that column and Group By to recombine.
Here's sample code you can paste into the Advanced Editor of a new blank query to see what I mean:
let Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjJU0lFyVIrVATKNgEwnCNMYyHRWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hci5EQAwCAOwXVy74dmGowD23yGBUoqAgChhC0eQDOiPVo6y9MI2jGUnXzkyHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, CSV = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"CSV", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"CSV", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CSV"), #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"CSV"}, Lookup, {"Key"}, "Lookup", JoinKind.LeftOuter), #"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Value"}, {"Value"}), #"Grouped Rows" = Table.Group(#"Expanded Lookup", {"ID"}, {{"CSV", each Text.Combine([CSV], ","), type nullable text}, {"Value", each List.Max([Value]), type nullable text}}) in #"Grouped Rows"
5 Replies
You would do it like this:
MyLookupTable{[KeyColumn="MyLookupValue"]}[ValueColumn]
You can replace "MyLookupValue" with any code.
What does the end result table that you want look like?
End table should look like this
a1,b1,c1 A
b2,c2,a2, B
b3, a3 C
I just tried to do it the following way - I changed the comma-separated column to a list in a new column, try to find indices of these in another table in another column and then do a search. It kind of worked, not ideal though
b4
You can split the comma-separated column into new rows, merge on that column and Group By to recombine.
Here's sample code you can paste into the Advanced Editor of a new blank query to see what I mean:
let Lookup = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSjJU0lFyVIrVATKNgEwnCNMYyHRWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, Value = _t]), Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Hci5EQAwCAOwXVy74dmGowD23yGBUoqAgChhC0eQDOiPVo6y9MI2jGUnXzkyHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, CSV = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"CSV", type text}}), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"CSV", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "CSV"), #"Merged Queries" = Table.NestedJoin(#"Split Column by Delimiter", {"CSV"}, Lookup, {"Key"}, "Lookup", JoinKind.LeftOuter), #"Expanded Lookup" = Table.ExpandTableColumn(#"Merged Queries", "Lookup", {"Value"}, {"Value"}), #"Grouped Rows" = Table.Group(#"Expanded Lookup", {"ID"}, {{"CSV", each Text.Combine([CSV], ","), type nullable text}, {"Value", each List.Max([Value]), type nullable text}}) in #"Grouped Rows"Thank you
