Forum Discussion
Using Text Comparison to Create Reference Column Based on Values of Another Tables Column
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"
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(_) )