compare to strings and find where is the difference

alex-JF — Tue, 30 Jul 2024 06:09:13 GMT

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

Re: compare to strings and find where is the difference

FreemanZ — Tue, 30 Jul 2024 06:54:14 GMT

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

topic compare to strings and find where is the difference in DAX Commands and Tips

compare to strings and find where is the difference

Re: compare to strings and find where is the difference