compare to strings and find where is the difference

[alex-JF]

So I've got a task to compare two columns which contains strings and find the difference. Haven't been able to find anything useful about this on the web, so I'll write here.

descr	trunk_rec_descr
bla blabla #AA=DNBIU# efwefr	#DS=123AS_123#
blaBLA #DE=WERE12WE# FQWER EWR	#DE=WERE12WE#

So I've got column "descr" which contains from part between # which is requires to be checked. This part got limitations - it is mostly 20 symbols long. Column "trunk_rec_descr" contains reference for each string to which left column should be compared.

Given my limited knowledge in dax? I've come with rather crude solution, but may be somebody will be interested or do something better.

so I make two additional columns:

string-compare = IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0),1)=MID(ip_int[trunk_rec_descr],1,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0),1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+1,1)=MID(ip_int[trunk_rec_descr],2,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+1,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+2,1)=MID(ip_int[trunk_rec_descr],3,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+2,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+3,1)=MID(ip_int[trunk_rec_descr],4,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+3,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+4,1)=MID(ip_int[trunk_rec_descr],5,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+4,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+5,1)=MID(ip_int[trunk_rec_descr],6,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+5,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+6,1)=MID(ip_int[trunk_rec_descr],7,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+6,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+7,1)=MID(ip_int[trunk_rec_descr],8,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+7,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+8,1)=MID(ip_int[trunk_rec_descr],9,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+8,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+9,1)=MID(ip_int[trunk_rec_descr],10,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+9,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+10,1)=MID(ip_int[trunk_rec_descr],11,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+10,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+11,1)=MID(ip_int[trunk_rec_descr],12,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+11,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+12,1)=MID(ip_int[trunk_rec_descr],13,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+12,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+13,1)=MID(ip_int[trunk_rec_descr],14,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+13,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+14,1)=MID(ip_int[trunk_rec_descr],15,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+14,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+15,1)=MID(ip_int[trunk_rec_descr],16,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+15,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+16,1)=MID(ip_int[trunk_rec_descr],17,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+16,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+17,1)=MID(ip_int[trunk_rec_descr],18,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+17,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+18,1)=MID(ip_int[trunk_rec_descr],19,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+18,1))))&

IF(SEARCH("#",ip_int[descr],1,0)=0,"N/A",IF(ip_int[tid]<1,"N/A",IF(MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+19,1)=MID(ip_int[trunk_rec_descr],20,1),"_.",MID(ip_int[descr],SEARCH("#",ip_int[descr],1,0)+19,1))))

and additional column to make things more clear:

descr-compare-clean = IF(SEARCH("N/A",ip_int[string-compare],1,0)>0,"N/A",IF(ip_int[descr_err]="OK","",IF(ip_int[string-compare]="_._._._._._._._._._._._._._._._._._._._.","match",MID(ip_int[string-compare],1,2*LEN(ip_int[trunk_rec_descr]))))))

result is like:

this solutions got some limitations for example if left part is shorter than right in comparison, but it is better then nothing

[FreemanZ]

hi @alex-JF ,

i would suggset you split the [descr] column by delimitor of "#" firstly in Power Query, then everything will become more intuitive. 

How: https://learn.microsoft.com/en-us/power-query/split-columns-delimiter