Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
@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 , Please find the formula capability matrix of direct query
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:
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.
@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.
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.
User | Count |
---|---|
130 | |
108 | |
101 | |
67 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |