cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
nirrobi Member
Member

Find the difference between 2 columns PQ / DAX

Hi all ,

I have table similar to the below.

I need to have have column that check if column 2 & 3 are identical - easy

I also need to have column that show the differnce between the columns

 

e.g.

1   identical

2   A - missing A

3   A - additional A

4 - ,  - additional ,

5 - space

 

is it possible to acheive with PQ / DAX ?

 

 

Column1Column2Column3

1AAAAAA
2AAAAA
3AAAAAAA
4ABAB,
5AAAA A
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Find the difference between 2 columns PQ / DAX

Hi @nirrobi,

 

You can add a custom column in Query Editor:

if ([Column1] = [Column2]) then
"identical"
else
if (Text.Length([Column1]) > Text.Length([Column2])) then
Text.Replace([Column1], [Column2], "") &"- missing "& Text.Replace([Column1], [Column2], "")
else
if (Text.Length([Column1]) < Text.Length([Column2])) then
Text.Replace([Column2], [Column1], "") &"- additional "& Text.Replace([Column2], [Column1], "")
else
""

This solves everything except point 5 in your example, but it's a good approach.

 

Regards.

1 REPLY 1
Highlighted
Anonymous
Not applicable

Re: Find the difference between 2 columns PQ / DAX

Hi @nirrobi,

 

You can add a custom column in Query Editor:

if ([Column1] = [Column2]) then
"identical"
else
if (Text.Length([Column1]) > Text.Length([Column2])) then
Text.Replace([Column1], [Column2], "") &"- missing "& Text.Replace([Column1], [Column2], "")
else
if (Text.Length([Column1]) < Text.Length([Column2])) then
Text.Replace([Column2], [Column1], "") &"- additional "& Text.Replace([Column2], [Column1], "")
else
""

This solves everything except point 5 in your example, but it's a good approach.

 

Regards.