Custom column to identify changes between two datasets

Hi 161521144121144 

 

There are many different ways to do this... consider this 2024Data

 

and this 2025Data

 

You can merge these queries using those 3 keys

And create a new column based on the result OR transform the result. Both are illustrated here.

let
    Source = Table.NestedJoin(
        #"2025Data", {"Product code", "Product description", "Attribute"}, 
        #"2024Data", {"Product code", "Product description", "Attribute"}, "Change", 
        JoinKind.LeftOuter
    ),
    NewColumn = Table.AddColumn(Source, "Custom", each 
        try 
            if Table.First([Change])[Weight source] <> [Weight source] 
            then "Changed" 
            else "Unchanged" 
        otherwise "Not Found"
    ),
    TransformedCol = Table.FromRecords( 
        Table.TransformRows( NewColumn, 
            each _ & [Change = try 
                if _[Change]{0}[Weight source] <> [Weight source] 
                then "Changed" 
                else "Unchanged" 
            otherwise "Not Found"] 
        )
    )
in
    TransformedCol

 

Another method could be to lookup all 3 keys, assuming a set of keys is unique.

let
    Source = #"2025Data",
    addColumn = Table.AddColumn(Source, "Change", each 
        try 
            /* Replace: #"2024Data" by your query name with 2024 data */
            if #"2024Data"{[Product code = [Product code], #"Product description" = [Product description], Attribute = [Attribute]]}[Weight source] <> [Weight source] 
            then "Changed" 
            else "Unchanged" 
        otherwise "Not Found" 
    )
in
    addColumn

 

Depending on the volume a merge will most likely be more performant...

Hi 161521144121144 ,

I have replicated this scenario using sample data with the following columns: Product code, Product description, Attribute, and Weight source.

Compare the 2024 and 2025 datasets to identify any changes in the Weight source field. Import both Data_2024 and Data_2025 tables into Power BI. Confirm both tables are included in the data model. Create a new calculated column in Data_2024 as follows:

Status =
VAR MatchRow =
    LOOKUPVALUE(
        Data_2025[Weight source],
        Data_2025[Product code], Data_2024[Product code],
        Data_2025[Product description], Data_2024[Product description],
        Data_2025[Attribute], Data_2024[Attribute]
    )
RETURN
IF(
    ISBLANK(MatchRow), "not found",
    IF(MatchRow = Data_2024[Weight source], "unchanged", "changed")
)

The LOOKUPVALUE() function searches for a corresponding row in Data_2025 based on Product code, Product description, and Attribute. If no match is found, the status is not found. If the Weight source values are identical, the status is unchanged. Otherwise, it is marked as changed.

Please find the attached .pbix file for your reference.