Forum Discussion

RyanHare92's avatar
RyanHare92
Icon for Helper I rankHelper I
2 years ago
Solved

Comparing values in columns and showing difference

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 fo...
  • ThxAlot's avatar
    ThxAlot
    2 years ago
    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"

RyanHare92's avatar
RyanHare92
Icon for Helper I rankHelper I
2 years ago

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.