Nested if in power query

[bilogin]

Column [customertypecodename] is a numeral that corresponds to the type of customer they are.

Customertypecode

1 = Competitor

3 = Customer

5 = Partner

6 = Influencer

8 = prospect

10 = Supplier

12 = Other

100000000 = Distributor

100000001 = Samples Only Address

100000002 = Prospect – No Good

The (truncated) formula I wrote was:

Customertypecodename = if[CustomerTypeCode]=1 then "Competitor" else if [customerTypeCode]=3 then "Customer" else "Partner"

The code changes to:

= Table.AddColumn(#"Added statecodename", "Customertypecodename", each if[CustomerTypeCode]=1 then "Competitor" else if [customerTypeCode]=3 then "Customer" else "Partner")

But it comes up with an error - "This step results in a query that is not supported in DirectQuery mode". Changing to Import isn't an option, there is waaaaay too much data. The syntax was correct before I saved it, I remember because I was so excited that I got it right first try! Little did she know. Grrrr.

[v-shex-msft · Accepted solution]

Hi @bilogin,

As parry2k said, directquery mode has limited the usage of the custom columns in query editor.
Perhaps you can try to do these replacements on the database side or add t-sql query in 'advanced option' -> 'SQL statement'.

Visualize data from Azure Data Explorer using a SQL query in Power BI 

Regards,

Xiaoxin Sheng

[parry2k]

@bilogin as the error states, you are using direct query connection and you cannot custom columns when using direct query. Read more here

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!