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
FrankAT's avatar
FrankAT
Icon for Community Champion rankCommunity Champion
6 years ago

Hi Sriku 

take a look at the following solution:

 

 

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZXLasMwEEV/JRi6G4NGb31Cu+reZBFooYYSAv1/qCxZtu/Y7iJ0ZRSdzOPeGXsYuvev8Xt8PMb7509HHTtSKj9VfXCsz549hZfuSkP3ev8Yb9OVrVeatNsce01cwbfb43bPv1gNIW0DLTmMqAOAhltuTQlJLHI9iXgKsM1RdIItn1CG3D5nb3acAq714J7M+r/R0Al9Qln064TyQPkzbTEln+VkTMp+9d49472D4i3ZKouTnqJqRWtWp12YSC6s3eaiDYKpXgSwIahledpOhEUOtnWHfDkGuTwsjeUpf7SKNIKLsCXSjF9wEQUSubQU23aJUDmzKxqlUGbOyCbQsjypZpLGWcl5FC/LrKeAyZFHEO3I4pp5xURAHWErDpuVA0VcFObsRdpJvI02F6GjaeYuoKvgZaUs76yd8/LfxUV81c3RotklFSs7D1ifjJwUjNgGdp3ltT6/59jsOFV3pnFtIVNsS4QfjoCF+rSYeESzwne9o1TKCGzaUMg/NN7GTXvJH8fXQvxmgAh6RF1/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Available = _t, DowntimeHrs = _t, Required = _t, #"Percentage%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Available", type number}, {"DowntimeHrs", type number}, {"Required", type number}, {"Percentage%", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Percentage%"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Percentage", 
each if [Country] = "India" 
then ([Available] - ([DowntimeHrs]/7.5) - [Required])/[Required]  
else ([Available] - ([DowntimeHrs]/6.5) - [Required])/[Required]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage", Percentage.Type}})
in
    #"Changed Type1"

 

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

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