Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
parry2k
Super User
Super User

@Anonymous 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
amitchandak
Super User
Super User

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

https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016?view=asallproducts-allversions

 

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@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. 

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

parry2k
Super User
Super User

@Anonymous 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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)

@Anonymous 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.