Forum Discussion
Sriku
Helper IV
6 years agoCalculate nested if in power query
Hi, I need to calculate Percentage in power query. If Country = India then (Available - (DowntimeHrs/7.5)- Required)/Required ) and for other country (Available - (DowntimeHrs/6.5)- Required)/Req...
AllisonKennedy
Community Champion
6 years agoYou can edit the formula bar for the built in conditional column. First add a conditional column like this:
Then edit the formula bar so it looks like this:
Otherwise, click the button to create a new CUSTOM COLUMN in the Add Column tab, and paste this formula into the box:
if [Required]= 0 then null else if [Country] = "India" then Value.Divide([Available] - [DowntimeHrs]/7.5 - [Required],[Required]) else Value.Divide([Available] - [DowntimeHrs]/6.5 - [Required],[Required])
Sriku UPDATE: Don't forget to set data type to number when you're done too.
- FrankAT6 years ago
Community Champion
thx for pointing to the exception, your formula is 👍.
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)