Forum Discussion
Formulating in power query
Hi!
I'm trying to formulate in power query the following issue:
From a column called "Difference" and the day I am, I want to add a column in which it tells me If I "executed" the activity, "not executed", is "planned" is "extra executed" or simply there's no plan "Unplanned".
So the idea is:
IF [Difference]=0 and not [YTDfinal]=0 then "Executed" If [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" IF [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" IF [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" IF [YTGfinal]>0 then "Planned" else "Unplanned".
I made the same formula in power bi desktop and it was ok:
Ejecutadoname = IF(AND(Cruce[Diferencia]=0,Cruce[YTD]<>0),"Executed",IF(AND(Cruce[Diferencia]<0,TODAY()>Cruce[Date]),"Extra executed",IF(AND(Cruce[Diferencia]>0,TODAY()>Cruce[Date]),"Not executed",IF(AND(Cruce[Diferencia]>0,TODAY()<=Cruce[Date]),"Planned",IF(Cruce[YTG]>0,"Planned","Unplanned")))))
But now that I want to rewrite the formula in power query it doesn't work
Hi carogomez
Try this:
= if [Difference]=0 and [YTDfinal]<>0 then "Executed" else if [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" else if [YTGfinal]>0 then "Planned" else "Unplanned"Not that M is case sensitive so if ... then... else has to be lower-case.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
1 Reply
Hi carogomez
Try this:
= if [Difference]=0 and [YTDfinal]<>0 then "Executed" else if [Difference]<0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Extra executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())>[fechainicio] then "Not executed" else if [Difference]>0 and DateTime.Date(Date.Time.LocalNow())<[fechainicio] then "Planned" else if [YTGfinal]>0 then "Planned" else "Unplanned"Not that M is case sensitive so if ... then... else has to be lower-case.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers