Forum Discussion
Power Query Date.AddMonths not working
Hi,
I'm struggling to make a date table which includes a column that has the date 6 months before.
I did it in Dax, but for the measures that follow after I need to make a comparison which isnt accepted in DirectQuery.
So I thought to do the comparison of dates in Power Query.
I have a table with a date column called "Date". I press the fX button in the top and enter this function
= Date.AddMonths([Date],-6)
It gives me an error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
How do I refer to the date column?
It's probably really easy but I'm not used to Power Query 😞
Hi Anonymous
That fx button is to invoke a custom function that you've already written, not to call inbuilt functions.
To deduct 6 months from your date column just click the Custom Column button
and add this in the code box
Regards
Phil
6 Replies
Anonymous , The syntax seems correct for power query
Date.AddMonths([Date],-6) //if Date is date data type
https://www.youtube.com/watch?v=S_meG32rDio
in case datetime try
Date.AddMonths(DateTime.Date([Date]),-6)
But not all functions work in Direct Query. But that should give import mode warning option
Hi amitchandak Thank you for your fast response. It was a DateTime field. But I still get the same error.
Do I need to define the field shorthand somewhere before it works?
Anonymous , I tried on Direct Query
Date.AddMonths(DateTime.Date([Date]),-1) is not supported in Direct Query
But I was able to do Date.AddMonths([Sales Date],-1) //but my date to not have time
Hi Anonymous
That fx button is to invoke a custom function that you've already written, not to call inbuilt functions.
To deduct 6 months from your date column just click the Custom Column button
and add this in the code box
Regards
Phil
