Custom Column - Nested IF / AND Formula

[Jgrohows]

Hello,

I am working on a  project where I need to provide a numerical score from 1-5 based on two criteria: (1) facility type (cateogried as Text) and (2) Cycle time (measured in days and categorized as Whole Number). I am new to PowerBi and still trying to learn the different syntax compared to Excel, but this is how i would write the formula using Excel:

=IF(AND(FacilityType="Hospital",CycleTime<10),5

   IF(AND(FacilityType="College",CycleTime<15,5

   IF(AND(FacilityType="Other",CycleTime<20,5

   IF(AND(FacilityType="Hospital",CycleTime<15),4

   IF(AND(FacilityType="College",CycleTime<20,4

   IF(AND(FacilityType="Other",CycleTime<25,4 

....... So on and so forth.

There are 5 different facility types and 6 different scores i want to provide. However I cant seem to make this work in Power Query as a new Cusotm Column. Does anyone have any suggestions on how to do this? 

Thank you!

Joe

[Jgrohows]

Thank you  very much!  I appreciate your quick and very helpful response. 

[tackytechtom · Accepted solution]

Hi @Jgrohows ,

It appears you are trying to use the DAX code language in Power Query, although Power Query uses a language called "M".

In your example, the code would look something similar to this:

if [FacilityType] ="Hospital" and [CycleTime] < 10 then 5 

else if [FacilityType] ="College" and [CycleTime] < 15 then 5 

else if ...

Note, M is case sensitive: "and" and "else" and "if" need to be written in small letters.

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/