Power Query - Multi If

TH123

New Member
Joined
Nov 15, 2022
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Hi,

I'm facing issue with formula in Power Query. I'd like to change excel formula from column G to power query but don't know how to write the code. Could you guys support? Sorry if information giving is not enough, pls let me know if any needed.

Thanks in advance.

Book1.xlsx
ABCDEF
1ABCDFG
29/26/2023100%EC001ECFALSENo
Sheet1
Cell Formulas
RangeFormula
D2D2=TRIM(LEFT([@C],MIN(FIND({0,1,2,3,4,5,6,7,8,9},[@C]&"0123456789"))-1))
E2E2=OR([@C]="EC002",[@C]="FP002")
F2F2=IF([@B]=0, IF([@A]>$B$2+2,"No",IF([@A]<=$B$2+2,"Yes, Need to update")), IF(OR([@D]="TD",[@D]="TOR",[@D]="WDR",[@D]="WDI",[@D]="WDP",[@D]="WDT"), IF([@B]>1.02,"YES, over more than 2%",IF([@B]<1,"YES, short in not allowed DIM","No")), IF([@F]=TRUE, IF([@B]>1.05,"YES, over more than 5%",IF([@B]<0.95,"YES, short more than 5%","No")), IF([@F]=FALSE, IF([@B]>1,"YES, over in not allowed DIM",IF([@B]<1,"YES, short in not allowed DIM","No"))))))
 

Attachments

  • Untitled 1.png
    Untitled 1.png
    2.7 KB · Views: 16

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
The general syntax is if [statement 1] and/or [statement 2] then [outcome 1] else [outcome 2].

Both outcomes can be new if statement-blocks. If you are unsure, then break it up into multiple columns (possibly using the Conditional Column action for help; you can also inspect the code that this generates), then consolidate them in the end.

Moreover, you will not be able to reference cell B2 directly, but you can import it as a parameter using this method: How To Reference A Named Range In Power Query | How To Excel. I have called it "Parameter" in the example here:

Power Query:
if [B] = 0
then if [A] > Parameter + 2
then "No"
else if [A] <= Parameter + 2
then "Yes, need to update"
else null
else null

Note that there must be an equal number of if, then and else's. I just put a few nulls instead of the rest of the formula for brevity.
 
Upvote 0
Hi Automatrix,

Sorry for late response & thanks for your support. May I consider else null = ")" in excel? If yes, then I insert my understanding in purple. Pls help to review & advise if it's correct. Thanks.

if = 0
then if [A] > Parameter + 2
then "No"
else if [A] <= Parameter + 2
then "Yes, need to update"
else null
else null
else if [D] = "TD" or [D] = "TOR" or ......then if > 1.02 then "Yes, over more than 2%" else if < 1 then "Yes, short in not allowed DIM" else "No" else null else null else
if [F] = true then
IF>1.05 then "YES, over more than 5%" else IF<0.95 then "YES, short more than 5%" else "No" else null else null else IF[F]=FALSE then IF>1 then "YES, over in not allowed DIM" else IF<1 then "YES, short in not allowed DIM" else "No" else null else null else null else null else null else null
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top