Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
kd_pandey
New Member

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

kd_pandey_0-1720616232212.png

 

table as below

 

TypeAmount_AAmount_BAmount_C
New8921  
Renew3703  
Renew427  
Renew7379  
Renew3148  
Renew787  
New2437  
New6799  
  15440
  79120
  96100
New2513  
New7962  
Renew4483  
Renew1296  
Renew4438  
Renew3202  
  07922
  01413
  04167
  05959
  06227
New843  
New6560  
New7359  
Renew5718  
  00
  00
  10971682
  5991133
New3569  
New3899  
New4583  
New7713  
New2874  
New2938  
1 ACCEPTED SOLUTION

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]

View solution in original post

6 REPLIES 6
BenjaminSNN
Frequent Visitor

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

BiAnalyst
Resolver I
Resolver I

You will have to add another custome column in Powerqueryas following,
= if [Type] <> null and [Type] <> "" then [Type]
else if [Amount C] <> 0 or [Amount C] <> null then "Renewed"
else if ([Amount B] = 0 or [Amount B] = null) and ([Amount C] = 0 or [Amount C] = null) then ""
else "Expiring"

Please accept it as solution if works.

this is the result of both solutions .. expring is missing in both 

kd_pandey_1-1720620251084.png

 

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]

my bad .. I changed it to 0 in last moment while uploading to make it more simple which missed your first line of code.. thank you so much 

 

Glad I could help!

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors