Forum Discussion

Sriku's avatar
Sriku
Icon for Helper IV rankHelper IV
6 years ago

Calculate 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...
Need Help
AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago
FrankAT you just need to account for the divide errors and infinity values still I think?
Sriku's avatar
Sriku
Icon for Helper IV rankHelper IV
6 years ago

Thanks for the solution. Now I am getting error due to infinity, How is overcome this issue.  Please suggest 

 

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Sriku Which solution are you referring to?

    This formula checks if the [Required] column is 0 first, and if it is then puts null as value, which should remove the infinity values. If it's not working, please share a screenshot of what you see and which rows have infinity and what formula you're using.

    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])