Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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)))

Need Help

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi johnt75 

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

       

      • johnt75's avatar
        johnt75
        Super User
        3 years ago

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

        if condition then result1 else result2