topic Re: Comparing values in columns and showing difference in DAX Commands and Tips

Comparing values in columns and showing difference

RyanHare92 — Fri, 02 Feb 2024 17:31:40 GMT

Hi,

I have two columns, each one of them has a list in each row of certain values. I want to compare the two columns and show which values in the second column are not present in the first. The following is an exaple:

I have managed to do this in Power Query, but I find it very messy. I've spent hours now trying to create a calculated column to do this, but I've had no success.

Re: Comparing values in columns and showing difference

RyanHare92 — Fri, 02 Feb 2024 19:03:56 GMT

Update:

I'm very close. So far, this is what I have:

New =

VAR FirstList =

IF(Candidates[old_components] = "-", BLANK(), Candidates[old_components] & ",")

VAR SecondList =

IF(Candidates[new_components] = "-", BLANK(), SUBSTITUTE(Candidates[new_components] & ",", " ", ""))

VAR UniqueValues =

CALCULATE(

CONCATENATEX(

GENERATESERIES(1, LEN(SecondList), 3),

VAR CurrentValueIndex = [Value]

VAR CurrentValue = TRIM(MID(SecondList, CurrentValueIndex, FIND(",", SecondList, CurrentValueIndex) - CurrentValueIndex))

RETURN

IF(

len(CurrentValue) <> 2,

BLANK(),

if(

ISBLANK(SEARCH(CurrentValue & ",", FirstList, 1, BLANK())),

CurrentValue,

BLANK()

)

", "

)

RETURN

SUBSTITUTE(UniqueValues, ", ,", "")

The only issue I'm having now is that it's concatenating a bunch of blank values. I need to find a way of not concatenating the value if the current value's length is smaller than 2, or a good way to clean up the result to get rid of the excess commas.

Re: Comparing values in columns and showing difference

ThxAlot — Sat, 03 Feb 2024 07:41:28 GMT

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUMTDSMTBW0oExTZRidYDiIJaOgSlIHCSvY2CmFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]), #"Added Diff" = Table.AddColumn(Source, "Diff", each Text.Combine(List.Difference(Text.Split([Column2],","), Text.Split([Column1],",")),",")) in #"Added Diff"