Formula issue: PowerBi Desktop New column vs PowerBi Desktop Power Query Editor new Custom Column

[Anonymous]

Hi,

I have a formula which I am able to apply as a new column in the PowerBi Desktop table itself but when I am trying to do the same in the Power Query Editor as a custom column, then I can't get it to work.

Can someone tell me what my error is?

Is there a logic error which I am considering?

Working formula within PowerBI "New column":

Wertaufschlag = IF(ISBLANK(afterbuy_sold_items_optimized_data[purchasePrice]), 0, IF(afterbuy_sold_items_optimized_data[purchasePrice]<2, ((afterbuy_sold_items_optimized_data[purchasePriceTotal]+afterbuy_sold_items_optimized_data[Pick&Pack_Total])*0.2), IF(afterbuy_sold_items_optimized_data[purchasePrice]>=2 && afterbuy_sold_items_optimized_data[purchasePrice]<10,((afterbuy_sold_items_optimized_data[purchasePriceTotal]+afterbuy_sold_items_optimized_data[Pick&Pack_Total])*0.2), IF(afterbuy_sold_items_optimized_data[purchasePrice]>=10 && afterbuy_sold_items_optimized_data[purchasePrice]<50, ((afterbuy_sold_items_optimized_data[purchasePriceTotal]+afterbuy_sold_items_optimized_data[Pick&Pack_Total])*0.15), IF(afterbuy_sold_items_optimized_data[purchasePrice]>=50, ((afterbuy_sold_items_optimized_data[purchasePriceTotal]+afterbuy_sold_items_optimized_data[Pick&Pack_Total])*0.1))))))

Attempted new formula within PowerBi Power Query Editor "Custom column": (NOT WORKING)

if([purchasePrice]=null,0,
if([purchasePrice]<2,(([purchasePriceTotal]+[#"Pick&Pack_Total"])*0.2))

if([purchasePrice]>=2 && [purchasePrice]<10,(([purchasePriceTotal]+[#"Pick&Pack_Total"])*0.2))

if([purchasePrice]>=10 && [purchasePrice]<50,(([purchasePriceTotal]+[#"Pick&Pack_Total"])*0.15))

if([purchasePrice]>=50,(([purchasePriceTotal]+[#"Pick&Pack_Total"])*0.1)))

[johnt75]

Try changing all instances of [#"Pick&Pack_Total"] with [Pick&Pack_Total]

[Anonymous]

Hi @johnt75 

I have tried the change but without change/success. The Error seems to be in the first row.

[johnt75]

That's the DAX syntax, the power query syntax is

if condition then result1 else result2