Forum Discussion
Solved
Using Text Comparison to Create Reference Column Based on Values of Another Tables Column
This is my first crack at requesting assistance, so I will do my best: I have two tables one with a couple million rows, the column in question has roughly 500 unique values (Table 1, Column A )...
A loop might be overkill. If the columns involved are text data type, then you can filter Column B using Text.StartsWith within the filter condition like this:
Using your original columns:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ0MDG3UIrViVYyMjA1MLFEZwIVGEMVgJgWhhCmobmxiRlMrTFMm6GxkQWUaWRkAGLGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Column A" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "New Column", (r) => List.First( List.Select( #"Table 2"[Column B], each Text.StartsWith(r[Column A], _) ), "" ), type text) in #"Added Custom"
AlexisOlson I do think this is closer, you are running into a similar issue I am though. How to get example (like row 5 ) to check that it can't match something more. row 5 should match 190381, not just 1903. would I enter something like a ' List.Last ' function
Yep. Change to List.Last instead of List.First.
A more sophisticated method would be to pick the longest match using List.Max with a Text.Length comparison rule.
(r) =>
List.Max(
List.Select(
#"Table 2"[Column B],
each Text.StartsWith(r[Column A], _)
), "", each Text.Length(_)
)