If statement

[Anonymous]

Hi all, 

I am importing data into power bi through power query and need to insert a custom column.

The formula i am trying to use is below.

IFERROR(IF([Days On Hand]>9, IFERROR(IF([Days On Hand]>9, ROUND([Inventory On Hand]/days [Days On Hand]*([Days On Hand]-9),0), 0),0)*[Standard Price], 0), 0)

However the formula is erroring and saying the 'IFERROR' is not recognized.

Could you help please.

Thanks in anticipation.

Sam

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

Actually, there are more details to be adjusted if you want to write this in M code.

IF -->  if if-condition then true-expression else false-expression

ROUND --> Number.Round

[v-yingjl · Accepted solution]

Hi @Anonymous ,

If you want to create custom columns in power query, the formula should be like this, your previous formula is used in DAX not power query:

= Table.AddColumn(#"Changed Type", "Custom", each if [Days On Hand] > 9 then Number.Round([Inventory On Hand] / [Days On Hand] * ([Days On Hand] - 9)) * [Standard Price] else 0)

Attached a sample file in the below, hopes it could help.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jimmy801]

Hello @Anonymous 

your formula is written in DAX and not power query. Use this formula in Power BI Desktop and not in Power Query, then it should work. Power Query is different.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Payeras_BI]

Hi @Anonymous ,

Use "try" "otherwise" instead.

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

Actually, there are more details to be adjusted if you want to write this in M code.

IF -->  if if-condition then true-expression else false-expression

ROUND --> Number.Round