Excel Formula to Power Query Custom Column Help!

[Delzy]

Hi all,

Im trying to load all my columns that have formulas in to power query but im struggling with translating the  excel formula for one column into a custom column Power query equivalent formula that gets the same result.

Hope that makes sense.

The excel formula is :

=IFERROR(IFS(OR([@[Race Code (2)]]="04",[@[Race Code (2)]]="DO",[@[Race Code (2)]]="IN",[@[Race Code (2)]]="LE",[@[Race Code (2)]]="RE"),"ADJU",[@[Race Code (2)]]="AF","AFAF",[@[Race Code (2)]]="AX","AXAX",OR([@[Race Code (2)]]="20",[@[Race Code (2)]]="HS",[@[Race Code (2)]]="NA"),"COMM",[@[Race Code (2)]]="EW","EWEW",[@[Race Code (2)]]="EX","EXEX",[@[Race Code (2)]]="FS","FSFS",[@[Race Code (2)]]="GS","GSGS",OR([@[Race Code (2)]]="GV",[@[Race Code (2)]]="UN"),"GVGV",OR(LEN([@[INV_REF]])=8,LEN([@[INV_REF]])=10,LEFT([@[INV_REF]],4),LEN([@[INV_REF]])=4),LEFT([@[INV_REF]],4),OR(LEN([@[INV_REF]])=9,LEN([@[INV_REF]])=6,LEN([@[INV_REF]])=14,LEN([@[INV_REF]])=15,LEN([@[INV_REF]])=24),"GVGV"),"ADJU")

Please note the below in the formula are column headers.

• Race Code (2)
• INV_REF

Can someone help me convert the excel formula into a Power Query Custom Column M Expression.

[v-yanjiang-msft]

Hi @Delzy ,

Based on the code you provided, not very clear about the expected result. Could you please provide a sample like below and the corresponding result?

Best Regards,
Community Support Team _ kalyj