How to detect the percentage of equal text in my column across rows

[asofiacgraca]

Hey, I am having some troubles here. Can someone help me, please?

I have some purchase orders and the description of each order. I am trying to figure out if there are purchase orders with like 80% match of their description. 

Help, please 🙂

[tamerj1 · Accepted solution]

@asofiacgraca 
Please try this version. I guess it matches your requirement.

Dealer (Standard) = 
VAR String1 = Table1[Dealer]
VAR Items1 = SUBSTITUTE ( String1, " ", "|" )
VAR Length1 = COALESCE ( PATHLENGTH ( Items1 ), 1 )
VAR T1 = GENERATESERIES ( 1, Length1, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items1, [Value] ) )
VAR T3 = 
    ADDCOLUMNS ( 
        VALUES ( Table1[Dealer] ),
        "@%Match",
        VAR String2 = Table1[Dealer]
        VAR Items2 = SUBSTITUTE ( String2, " ", "|" )
        VAR Length2 = COALESCE ( PATHLENGTH ( Items2 ), 1 )
        VAR T4 = GENERATESERIES ( 1, Length2, 1 )
        VAR T5 = SELECTCOLUMNS ( T4, "@Item", PATHITEM ( Items2, [Value] ) )
        VAR T6 = INTERSECT ( T5, T2 )
        RETURN
            DIVIDE ( COUNTROWS ( T6 ), COUNTROWS ( T5 ) )
    )
VAR T7 = FILTER ( T3, [@%Match] >= 0.8 )
RETURN
    COUNTROWS ( T7 )

[asofiacgraca]

Hey @tamerj1 

That seem to be working on my data set! Many thanks 😊

However, on my real data as it it a large set it's not running, do you know how to accelarate the process?

Thanks!

[tamerj1]

@asofiacgraca 

Yes it is a pretty heavy calculation and won't work for big data. Not sure if can improved as DAX is not designed to perform such type of calculations. 

[tamerj1]

Hi @asofiacgraca 
Attached is an example of best match calcuation using DAX.

[asofiacgraca]

Hey @tamerj1 

Thank you for responding to my question. I am having some troubles tho. The table Standard is supposed to be created on VAR T3, right? I can't do that on my data, it only lets me use existing data on the first parameter. Can you help me?

[tamerj1]

@asofiacgraca 
Please provide some screenshots for better understanding. 

[asofiacgraca]

Let's suppose I have this table with this data:

If I try to add your code in a calculated collumn on my table i have this error because the paremeter isnt the right type (i only have one table - table 1). 

[tamerj1]

@asofiacgraca 
Actually your requirement seems to be much simplier than best match pattern. Would you share some sample data or sample file to work with?

[asofiacgraca]

How can i attach a file here? having troubles doing so

[v-yueyunzh-msft]

Hi , @asofiacgraca 

According to your description, you want to detect the percentage of equal text in my column across rows.

For your needs, in Power Query we mainly perform data transformation, if your text is a statement, then it may be difficult to directly use Power Query to calculate text similarity.

For comparing two separate words, we can use Text.ToList() to get a list of corresponding texts, then List.Intersect() to get the same string, and then use division to calculate text similarity.

For more information, you can refer to :
Solved: Compare two strings for similarity - Microsoft Power BI Community

For statement comparison, you can try to find a third-party API, and then use Web.Contents to call the API in Power Query to get the corresponding text similarity.

For more information, you can refer to :
Web.Contents - PowerQuery M | Microsoft Learn

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[asofiacgraca]

Hey @v-yueyunzh-msft 

Thanks for your answer!
Yes, i am trying for the statement comparision 😕

Best regards 🙂