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.
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)
@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.
@zguo7 , Please find the formula capability matrix of direct query
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|
@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.
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Learn about the exciting things that happened in July.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.
We have great updates this month! Click the link for the video with more info.