Forum Discussion

kd_pandey's avatar
kd_pandey
Regular Visitor
2 years ago
Solved

IF and Statement in Excel Power Query

I am trying to create below scenario in excel power query, where I'm getting when trying to create Point. 2 & 3   table as below   Type Amount_A Amount_B Amount_C New 8921     ...
Need Help
  • BenjaminSNN's avatar
    BenjaminSNN
    2 years ago

    in the example data you gave the Amount_A values were null.

    Changed the code so it works with = 0 too 🙂

    if [Amount_A] = null or [Amount_A] = 0  then
        if [Amount_C] = null or [Amount_C] = 0 then
            if [Amount_B] = 0 or [Amount_B] = null

                  then null
                  else "Expiring"
           else "Renewed"
    else [Type]

BenjaminSNN's avatar
BenjaminSNN
Frequent Visitor
2 years ago

I think this should do the trick. Add a new custom column with the following code:

if [Amount_A] = null then
    if [Amount_C] = null or [Amount_C] = 0 then
        if [Amount_B] = 0 or [Amount_B] = null

              then null
              else "Expiring"
       else "Renewed"
else [Type]


Tip: Basing logic on the False premise (C <> 0) often makes things more complicated. Try to build your logical from the true (C = 0) premise