Show number of coincidences between two columns

[RIBUZ]

Hello Community

I have two columns, "Manual Serial" which is a column coming from people's entries and it may have some computing errors and on the other hand, the another column called "Correct Serial" which is a column coming from SAP and belongs to the correct serial.

I am looking for a formula in Power Query (not DAX) that can give me the number of coincidences between "Manual Serial" and "Correct Serial" by matching letters and number. The only complexity here is that I want the scan of "Correct Serial" is from right to left as well as items should not be counted twice. Below the sample table and in red how the coincidences look like. Spaces are represented by (_).

I gave up trying to do this. Really appreciate you can help me out please.

[RIBUZ]

@tamerj1  Thank you again and I'll follow you with your suggestion.

I just realized that this code is having a conflic in this scenario:

Manual Serial	Corrected Serial	Coincides
00030002	29106458WHBK00030002	8
00030002	29053528WHBK00020049	8
00030002	29053528WHBK00020005	8

Is there a way how I can make the corrected serial 29106458WHBK00030002 can get the higuer value? I would keep the previous formulas because they cleaned a lot of data. This is a particular a scenario, maybe there can be a second column as a second round? not necesarily embedded into this one.

The idea at the end is to select the row with the highest value because that would be the corrected one. 

Many thanks ! 

[tamerj1]

@RIBUZ 
Why not use the followingas a first step?

[tamerj1 · Accepted solution]

Hi @RIBUZ 
Please refer to attached sample file with the solution

Coincides = 
VAR ManualString = SUBSTITUTE ( 'Table'[Manual Serial], " ", "_" )
VAR Length1 = COALESCE ( LEN ( ManualString ), 1 )
VAR T1 = GENERATESERIES ( 1, Length1, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item1", MID ( ManualString, [Value], 1 ) )
VAR T3 = GROUPBY ( T2, [@Item1], "@Count1", COUNTX ( CURRENTGROUP ( ), 1 ) )
VAR CorrectString = SUBSTITUTE ( 'Table'[Correct Serial], " ", "_" )
VAR Length2 = COALESCE ( LEN ( CorrectString ), 1 )
VAR T4 = GENERATESERIES ( 1, Length2, 1 )
VAR T5 = SELECTCOLUMNS ( T4, "@Item2", MID ( CorrectString, [Value], 1 ) )
VAR T6 = GROUPBY ( T5, [@Item2], "@Count2", COUNTX ( CURRENTGROUP ( ), 1 ) )
RETURN 
    SUMX ( 
        T6,
        VAR T7 = FILTER ( T3, [@Item1] = [@Item2] )
        VAR Count1 = MAXX ( T7, [@Count1] )
        VAR Count2 = [@Count2]
        RETURN
            MIN ( Count1, Count2 )
    )

[RIBUZ]

Hello @tamerj1 , thank you very much for the code, as far as I could test it, it is working great. Just out of curiosity, is there any way to replicate this in power query? I would like to have this column agregation in the background, in the transform data view. Thanks

[tamerj1]

@RIBUZ 

Even if possible I can imagine that it would be have very bad performance with power query. Dax performs much faster with such complex queries. However just allow for other more Power query expert people to provide their solutions. Or you can post the question in the power query forum where the top power heros can better support you.