I have been doing some work with Power BI Composite models, and what I have found is that often the DirectQuery table can be changed to an Imported table.

I personally feel that this blog post will be an important reference for me and hopefully others when you start to use Power BI Composite Models, to ensure that the DirectQuery tables remain as DirectQuery.

I did previously briefly discuss this in my blog post called Quick Tips for Aggregations & Composite Models in Power BI, this goes into more details around how to ensure it stays in DirectQuery mode.

In my example I am going to be getting data from SQL Server, which is one of the supported DirectQuery databases.

Here is a link to all the existing (and future supported databases): Data sources supported by DirectQuery in Power BI

Creating a table in DirectQuery Mode

The first thing is to ensure that my table is in DirectQuery mode is to follow the steps below.

  • I click on Get Data, then select SQL Server Database and click Connect
  • I then put in my Server and Database
    • In the above I had to ensure that I selected DirectQuery
    • I also made sure that I left the SQL Statement blank.
    • And for good measure I also removed the default tick of “Include relationship columns”
  • I then clicked Ok
  • I then selected my table or view, in my example I selected a view I created called (Very Original!) vw_DirectQueryTest
  • I selected the above and clicked load

Validating that my table is in DirectQuery Mode

I then wanted to check and ensure that it is in DirectQuery Mode

  • The first way I did this was in my data model, I right clicked on the table and selected Properties
    • I could then see in the Field Properties that the Storage Mode is set to DirectQuery
  • NOTE: I would suggest checking this before saving your PBIX, this will ensure if it is NOT in DirectQuery mode to resolve any table issues.
  • The second place that I could verify that it is in DirectQuery Mode is to go into the Power Query Editor, by clicking on Edit Queries
    • In my table I go to the APPLIED STEPS and right clicked again and selected View Native Query
    • NOTE: If you cannot select “View Native Query” then the table will not be in DirectQuery Mode

Changes that will affect the storage Mode

Below is a list of changes that can change the storage mode of the table from DirectQuery to Import

Putting in my own Transact-SQL (TSQL) will change the table storage Mode to Import

  • If I decide to write my own TSQL instead of using the default Navigation Pane, this will change my table storage mode to import.
  • As shown below I put in my own TSQL
  • Now when I went back to my APPLIED STEPS, the View Native Query was disabled

Certain Power Query M functions that are not supported will change the table storage mode to Import

I am not sure if it is documented in terms of which Power Query functions will change the storage mode from DirectQuery to Import. If anyone has this reference, please let me know and I will update this blog post.

Below is an example what happens when I put in a new Merged Column

  • I added the following Merged column in my DirectQuery Table
  • Once I had applied this step, I then got the following presented to me
    • If I then clicked on the button “Switch all tables to Import mode” I was prompted with a screen letting me know how this will affect my model.
    • I then went back and deleted the step which removed the prompt and my table stayed as DirectQuery
  • It should be noted that some Power Query M functions will keep the table storage mode in DirectQuery
    • As shown below I changed the data type from DateTime to Date and the table is still in DirectQuery storage mode.
    • If I click on View Native Query I can see how it modified the TSQL syntax

Conclusion

In this blog post I have shown how to ensure that my tables remained in DirectQuery mode, which helps me when working specifically with Power BI composite models.

If I was unsure if it a table storage mode was in DirectQuery or not, I would always go and look in the data model, then select the table properties and ensure that it is set to DirectQuery. This ensures that when the data is being accessed it will always go directly to the source.

If anyone has other suggestions or tips to add to this blog post, please let me know and I will happily add them with credit to you.

Once again thanks for reading and I hope it was informative.