Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CRBailey
Regular Visitor

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 AColumn B
190478117
205049206
2050491903
1903781904
190381190381
1173462173
2053492174
2132892130
2220892132

 

1 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:

 

AlexisOlson_0-1637259198775.png

 

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_1-1637259426268.png

View solution in original post

8 REPLIES 8
CRBailey
Regular Visitor

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.

CRBailey_0-1637247109942.png

 

@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 ""

 

 

smpa01_0-1637252705459.png

 

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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:

 

AlexisOlson_0-1637259198775.png

 

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_1-1637259426268.png

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.

 

 

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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...

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

 

AlexisOlson
Super User
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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors