"Translate" from MS Excel "IF" & "AND" & "OR" for a calculated column

[Orstenpowers]

Dear all,

I hope you can help me...

My data also consist of a column “Order No”, being defined as Text.

Normally such an order number starts with “010”, “020” etc., sometimes also with “SE*…” or “MI*…”

Now I do need a solution how to identify specific order numbers and return text “Sales Order Germany” as result.

In Excel I would have written as follows, but I do not get it translated into PBI language…

IF

'Table'[Order No] begins with “010”   OR

'Table'[Order No] begins with “SE*01”   OR
'Table'[Order No] begins with “MI*” AND ends with “*101”

THEN
“Sales Order Germany”

Is there anybody out there who can help me? Amongst others, my attempts failed, because “*” was interpreted as replacement character…

Looking forward to your proposal I remain

Best regards

Orstenpowers

[v-huijiey-msft]

Hi @Orstenpowers ,

Thanks to @Kishore_KVN and @Kishore_KVN for their replies, they both made good points.

Unlike Excel, DAX does not use wildcards like "*" in the same way for pattern matching. Instead, we will focus on the presence of specific text strings.

After my testing, I have found two ways to accomplish your needs.

One is to add a new column in Power Query as @_AAndrade said, and he is right in what he said and how he did it.

One is like @Kishore_KVN said, New Column in Data view. his method is right, but there is some error in syntax:

The modified syntax is as follows, Please try:

Status = 
If(Left('Table'[Order No],3) = "010" || Left('Table'[Order No],5) = "SE*01" || Left('Table'[Order No],3) = "MI*" && Right('Table'[Order No],4) = "* 101", "Sales Order Germany", "Others")

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Kishore_KVN]

Hello @Orstenpowers ,

you can try below calculation bit modified as per your table names and column names in a calculated column it will solve your requirement mostly. 

Status = 
If(Left('Table'[Order No],3) = "010" || Left('Table'[Order No],5) = "SE*01" || Left('Table'[Order No],3) = "MI*" && Right('Table'[Order No],4) = "*101),"Sales Order Germany","Others")

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[_AAndrade · Accepted solution]

Hi,

Try to add a new column on Power Query using this code:

= Table.AddColumn(
    #"Previous Step",
    "New Column",
    each if Text.StartsWith([Order No], "010") or Text.StartsWith([Order No], "SE*01") or (Text.StartsWith([Order No], "MI*") and Text.EndsWith([Order No], "*101")) then "Sales Order Germany" else null
)