Custom column to identify changes between two datasets

[161521144121144]

Hi, I'm new to PowerBI and need some help with creating a formula in custom column. I have two datasets - one from 2024 and one from 2025 which contain identical column headings. I want the formula to look for the product code, product description and attribute in both datasets and if these match but the weight source has changed return "changed", if the weight source is the same return "unchanged" and if the product code, product description and attribute do not appear in the 2025 data return "not found". Hopefully this makes sense!

My data looks like this:

Product code	Product description	Attribute	Weight source
1234	Yellow	Primary	Global
1234	Yellow	Secondary	Inhouse
1236	Blue	Primary	Global

[v-sshirivolu]

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.

[161521144121144]

Thank you. Trying to repeat the formula gives Token Eof expected for the MatchRow after VAR

[v-sshirivolu · Accepted solution]

Hi @161521144121144 ,

The Token Eof expected error usually happens when there’s a small syntax issue in your DAX formula like missing line breaks, extra spaces, or parentheses that don’t match. In your case, it looks like the problem might have come from how the line breaks or indentation got copied over.

Below is a cleaned up version of your formula that should work if you paste it directly into a Power BI calculated column:

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

[m_dekorte]

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...

[161521144121144]

Thanks, trying the new column and transforming the result both give an expression error: The name 'TransformedCol' wasn't recognised - how would I get around this?

[m_dekorte]

You are referencing the identifier TransformedCol (I presume after the in clause), that's the step name on the last line in the Advanced Editor. Make sure to update it to NewColumn if that is the last step in your query.