Forum Discussion
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 ). I have another table with a column of 50 unique values that are portions of some of the values found in 'Column A' (Table 2, Column B). My thought is that I would create a column in Table 1 that compares values in Column A to those that start with values in Table 2 Column B and if a match is found, returns value from Table 2 Column B. I have converted both columns to text, but can't seem to find the proper syntax to complete this task.
I am very green when it comes to DAX and Power Query so I may be going about it all wrong. Any assistance or recommendations would be greatly appreciated. I can try and provide better / clearer information if the below doesn't suffice.
| Column A | Column B |
| 190478 | 117 |
| 205049 | 206 |
| 205049 | 1903 |
| 190378 | 1904 |
| 190381 | 190381 |
| 117346 | 2173 |
| 205349 | 2174 |
| 213289 | 2130 |
| 222089 | 2132 |
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"
8 Replies
- AlexisOlson
Super User
I'd recommend showing a sample of both tables and what your desired result is. It's not clear what to do with the Column A and Column B you've provided.
Apologies,
trying to find the best way to provide the requested information. My responses to not appear to be saved when I provided them. Hopefully this one works and the below information is more helpful.
My thought is the function should be something to the effect of:
New Column = IF Table 1, Column A Text.StartsWith Table 2, Column B then return Table 2,Column B
I know this is not anywhere near correct, but hopefully gives you an Idea of what I am looking to accomplish.
Example of data and expected result.
smpa01Community Champion
CRBailey you can run a loop to do the job where it terminates at the first match; pbix is attached
let value = Text.From([ColumnA]), Loop = List.Generate( ()=>[i=0,j=Text.StartsWith(value,Search{i})], each [j]<>true, each[i=[i]+1,j=Text.StartsWith(value,Search{i})], each [i] ), index1= if List.Count(Loop)-1 =-1 then 0 else List.Count(Loop)-1, index2 = if index1=0 then 0 else index1+1 in try Search{index2} otherwise ""- AlexisOlson
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"
