Forum Discussion

bilogin's avatar
bilogin
Helper I
6 years ago
Solved

Nested if in power query

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.

 

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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!