Power Query - Multi If

[TH123]

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
	A	B	C	D	E	F
1	A	B	C	D	F	G
2	9/26/2023	100%	EC001	EC	FALSE	No
Sheet1

Cell Formulas
Range	Formula
D2	D2	=TRIM(LEFT([@C],MIN(FIND({0,1,2,3,4,5,6,7,8,9},[@C]&"0123456789"))-1))
E2	E2	=OR([@C]="EC002",[@C]="FP002")
F2	F2	=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"))))))

 

[Automatrix]

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:

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.

 

[TH123]

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

 

[TH123]

Hi, Can anyone help me?