Forum Discussion

siddrow's avatar
siddrow
Icon for Helper III rankHelper III
3 years ago

Help with Power Query custom column creation - formula error

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 [Str...
Need Help
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
3 years ago

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