Forum Discussion
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
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.⚡
7 Replies
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.⚡
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.⚡
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:
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
YEARamitchandak not sure how your reply is relevant to the question. Did you saw Anonymous's reply and the issue?
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.
