Forum Discussion

RyanHare92's avatar
RyanHare92
Helper 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 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. 


  • 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"

2 Replies

  • RyanHare92's avatar
    RyanHare92
    Helper 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.




  • ThxAlot's avatar
    ThxAlot
    Super User
    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"