Simple issue: whole number comparison

[RMDNA]

All,

I'm trying to compare years between two tables, so I've separated the year out from the date type and set it to whole number datatype. I'm trying to create a simple column of "if this year doesn't equal the other year, then "Text" else "Text2".

In PowerQuery custom column, I've done:

if(
[Fiscal Year 1 Number] <> "Table 2"[Fiscal Year 2 Number]
)
then "Yes" else "No"

I get:

Expression.Error: We cannot apply field access to the type Text.
Details:
    Value=Table 2
    Key=Fiscal Year 2

Both fiscal year values are set to whole number. Any help?

[v-ljerr-msft]

Hi @RMDNA,

Is there any relationship between your Table 1 and Table 2? If there is a "Many to one" (*:1) relationship between Table 1 and Table 2, then you should be able to simply use the following DAX formula to add a calculate column in Table 1 under Modeling tab. 

New Column =
IF (
    'Table 1'[Fiscal Year 1 Number] <> RELATED ( 'Table 2'[Fiscal Year 2 Number] ),
    "Yes",
    "No"
)

Regards

[MarcelBeug]

I guess this code is in query "Table 1"?

If you want to refer to "Table 2" you need a # (#"Table 2"), but then again you are refering to a complete column in Table 2, or - in other words - to a list of values.

Somehow you need to get individual values, typically by merging the 2 tables so you have values from Table 1 on the same row as the values from Table 2. Then you can simply use:

if(
[Fiscal Year 1 Number] <> [Fiscal Year 2 Number]
)
then "Yes" else "No"