Using Text Comparison to Create Reference Column Based on Values of Another Tables Column

[CRBailey]

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

[CRBailey]

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.

[smpa01]

@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 · Accepted solution]

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"

[smpa01]

Thanks @AlexisOlson 

Can you please explain this bit where you used r

(r) =>
        List.First(
            List.Select(
                t2[ColumnB],
                each Text.StartsWith(r[ColumnA], _)
            ),"")

I have seen this usage in PQ but don't completely undertsand what it is doing and how it is influencing the calculation.

[AlexisOlson]

Because I'm working with two different environments at the same time (the current row and the current item in a list), I use a named function to distinguish between them. Without the "r" inside Text.StartsWith, it thinks I'm trying to refer to [Column A] of the current item.

The "each ... _" construction is equivalent to "(_) =>" so I could have written it like this instead:

(row) =>
    List.First(
        List.Select(
            t2[ColumnB],
            (item) => Text.StartsWith(row[ColumnA], item)
        ),"")

Recommended reading:
https://bengribaudo.com/blog/2017/12/08/4270/power-query-m-primer-part3-functions-function-values-pa...

[CRBailey]

@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

[AlexisOlson]

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(_)
    )

[AlexisOlson]

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.