Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
RyanHare92
Helper I
Helper I

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:

RyanHare92_0-1706895090341.png

 

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. 


1 ACCEPTED SOLUTION
ThxAlot
Super User
Super User

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"

ThxAlot_0-1706946170570.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



View solution in original post

2 REPLIES 2
ThxAlot
Super User
Super User

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"

ThxAlot_0-1706946170570.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LearnAndPractise(Everyday)


)



RyanHare92
Helper I
Helper I

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.




Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.