cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Limitations when create table based on custom direct query?

Hi

 

I have a couple SQL server tables contains many many columns. I'd like to create tables with DirectQuery and also specify the query statement, which is pretty simple like

 

select column1, column2...column30 from table1;

 

The table can be created in PBI without any issues, but when make transformation, for example, add condtional columns, power BI threw following errors

 

"This step results in a query that is not supported in DirectQuery Mode" with "Switch all tables to import mode" button.

 

Is there any work around for this issue? Besides any document to specify the limitation in the DirectQuery Mode? 

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@zguo7 no, you cannot add column in direct query mode, why not add these columns in the backend and take full advantage of DQ. Here are the DQ limitations.

 

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!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@zguo7 no, you cannot add column in direct query mode, why not add these columns in the backend and take full advantage of DQ. Here are the DQ limitations.

 

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!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Regular Visitor

Re: Limitations when create table based on custom direct query?

Thanks for the quick feedback.

 

But that's interesting, because I can add conditional column in DirectQuery mode if I don't use custom query but instead choose the table from SQL server DB table list. I can see the difference is like this

 

With DirectQuery and cusotm query:

= Table.AddColumn(Source, "IsTrueOrFalse", each if [column01] = "01" then "True" else if [column01] <> "01" then "False" else null)

 

With DirectQuery and choose a table:

= Table.AddColumn(dbo_theTableName, "IsTrueOrFalse", each if [column01] = "01" then "True" else if [column01] <> "01" then "False" else null)

Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@zguo7 it is weird, that is just a step name, not a big deal. Hmm. I have to test it. One way, you can create a view in the backend with the columns you need, and instead of writing queries directly in Power BI, just use the view and then add the custom column.

 

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!

Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@zguo7 , Please find the formula capability matrix of direct query 

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc...

 

refer

These are functions that primarily return scalar or aggregate results. These functions are further divided into those that are supported in all types of formulas: measures, queries, calculated columns, row level security, and those that are supported in measure and query formulas only. These include:

Optimized for DirectQuery
Supported in all DAX formulas Supported in measure and query formulas only
ABS
ACOS
ACOT
AND
ASIN
ATAN
BLANK
CEILING
CONCATENATE
CONTAINSSTRING
COS
COT
CURRENCY
DATE
DATEDIFF
DATEVALUE
DAY
DEGREES
DIVIDE
EDATE
EOMONTH
EXACT
EXP
FALSE
FIND
HOUR
IF
INT
ISBLANK
ISO.CEILING
KEEPFILTERS
LEFT
LEN
LN
LOG
LOG10
LOWER
MAX
MID
MIN
MINUTE
MOD
MONTH
MROUND
NOT
NOW
OR
PI
POWER
QUOTIENT
RADIANS
RAND
RELATED
REPT
RIGHT
ROUND
ROUNDDOWN
ROUNDUP
SEARCH
SECOND
SIGN
SIN
SQRT
SQRTPI
SUBSTITUTE
SWITCH
TAN
TIME
TIMEVALUE
TODAY
TRIM
TRUE
TRUNC
UNICODE
UPPER
USERNAME
USERELATIONSHIP
VALUE
WEEKDAY
WEEKNUM
YEAR


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@amitchandak not sure how your reply is relevant to the question. Did you saw @zguo7's reply and the issue?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@parry2k , when I start typying there was only one reply. When I came back saved, there were few replies, so missed those. 

In between jumped on few other topics. 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: Limitations when create table based on custom direct query?

@amitchandak sounds good. Just making sure that we are not replying coz for the sake of replying and not helping end-user. Even the original post was pretty clear. Anyhow, the reason I asked because I always want to know that I didn't miss understanding the original question/problem posted by the user.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Power BI Desktop August 2020 Update

Power BI Desktop August 2020 Update

We have great updates this month! Click the link for the video with more info.

Top Solution Authors
Top Kudoed Authors