Forum Discussion
Custom column to identify changes between two datasets
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")
)
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...
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?
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.
