Help with Power Query custom column creation - formula error

[siddrow]

Hi

I am trying to do the below in a custom column and getting this error. I'm assuming I can't minus a financial year from a date?

=if [Current Financial Year] <= [FY Final 3] then [Strategy Years] - [Current Financial Year] - [Date Last Changed]

else [Strategy Years] - [Current Financial Year] - [FY Final 2]

Here are my column formats in Power Query:

• Current Financial Year - number (year is showing as 2023 format)
• FY Final 3  - number (year is showing as 2023 format)
• Strategy Years  - decimal number as some of the years contain decimal values
•  Date Last Changed  - date format (20/10/2023)
• FY Final 2  - number (year showing as 23)

Any help would be much appreciated, thanks 🙂

[v-jingzhang]

Hi @siddrow 

In Power Query, a number value cannot minus a date value. So you saw the error "We cannot apply operator - to types Number and Date". I did a test with the values shown in your error message picture. "C1" shows the result in Excel after I converted the result to Number type. 

If this result is what you want, you can use Number.From to convert the date value into a number value, as Pete has suggested, then subtract it from the left number value. This will give you the same result as in Excel. 

Best Regards,
Community Support Team _ Jing

[BA_Pete]

Hi @siddrow ,

I honestly have no idea what this calculation is supposed to achieve, but you can try converting your date into a number within the calculation by putting Number.From( ) around it: Number.From([Date Last Changed])

Pete

[siddrow]

If the first condition  is met, i want it to calculate  [Strategy Years] - [Current Financial Year] - [Date Last Changed] otehrwise if it doesn't i want it to calculate [Strategy Years] - [Current Financial Year] - [FY Final 2]

Not sure how to explain it any other way. This is the excel formula that i've tried to convert it from - not sure if this helps either. I will try your suggestion and see if it changes anything. thank you

=IFERROR(IF(AX$3<=AW19),$AT19-(AX$3+$AU19),$AT19-(AX$3-($AW19))),"")

[BA_Pete]

Hi @siddrow ,

Actually, I think seeing that Excel formula does help. The first thing I notice is that your row references are not the same (3 & 19) and that some have fixed ($) references, which means you'll struggle to replicate it unless you've either already transformed your data into matching rows, or you're using separate query references.

If my previous Number.From() suggestion doesn't work for you then, if you can provide an example of the Excel data (screenshot will probably do, including row and column headers) that the Excel formula is working off, and also a copyable example of your Power Query source data, I should be able to put a working solution together for you.

Pete