Forum Discussion
Lookup value that contains substring from another table
Hello,
I have table 1 that has a bunch of addresses
| Address | Value |
| St George Street 12 | ..... |
| 5th Avenue South 3A | ...... |
I have table 2 that has a bunch of street names and their zip codes:
| Street name | Zip Code |
| St George Street | 32025 |
I want to create a new column in Table 1 with the Zip code from Table 2. How can I achieve this?
Thank you in advance!
Hi Anonymous ,
you can add a column with the following formula:
Table.SelectRows(Table2, (x) => Text.Contains([Address], x[Street name])){0}[Zip Code]This will return the first match, if there is any. Otherwise error. Just add some error-handling or remove errors afterwards.
For performance reasons, make sure to buffer Table2.
7 Replies
Hi Anonymous ,
you can add a column with the following formula:
Table.SelectRows(Table2, (x) => Text.Contains([Address], x[Street name])){0}[Zip Code]This will return the first match, if there is any. Otherwise error. Just add some error-handling or remove errors afterwards.
For performance reasons, make sure to buffer Table2.
Hi ImkeF
Thank you very much for your reply. However I got the error:
Expression.Error: There weren't enough elements in the enumeration to complete the operation.When I added the code as a custom column. Is there any way to fix this?
Hi Anonymous ,
it worked for me on my sample data. So there is a good chance that you didn't apply it correctly.
Please share our full code or the file for me to check.
Hi Anonymous ,
Basically the query mentioned by @ ImkeF should work, or you can try to use fuzzy merge:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi5RcE/NL0pPVQguKUpNLVEwNFLSUTI0VYrViVYyLclQcCxLzSsFyuaXAjnGjkBJUwOwJKZOU6CkEVBnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}, {"Value", Int64.Type}}), #"Merged Queries" = Table.FuzzyNestedJoin(#"Changed Type", {"Address"}, #"Table 2", {"Street name"}, "Table 2", JoinKind.LeftOuter, [IgnoreCase=true, IgnoreSpace=true]), #"Expanded Table 2" = Table.ExpandTableColumn(#"Merged Queries", "Table 2", {"Zip Code"}, {"Zip Code"}) in #"Expanded Table 2"Refer:
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
