Forum Discussion
Solved
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
| 1 | AAA | AAA |
| 2 | AAA | AA |
| 3 | AAA | AAAA |
| 4 | AB | AB, |
| 5 | AAA | A A |
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
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.