Formulating in power query

[carogomez]

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

[AlB · Accepted solution]

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