cancel
Showing results 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

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

table as below

 Type Amount_A Amount_B Amount_C New 8921 Renew 3703 Renew 427 Renew 7379 Renew 3148 Renew 787 New 2437 New 6799 1544 0 7912 0 9610 0 New 2513 New 7962 Renew 4483 Renew 1296 Renew 4438 Renew 3202 0 7922 0 1413 0 4167 0 5959 0 6227 New 843 New 6560 New 7359 Renew 5718 0 0 0 0 1097 1682 5991 133 New 3569 New 3899 New 4583 New 7713 New 2874 New 2938
1 ACCEPTED SOLUTION
Frequent Visitor

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]

6 REPLIES 6
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

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.

New Member

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

Frequent Visitor

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]

New Member

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

Frequent Visitor

Announcements

#### 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.

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors