Questions on converting an 'Import Mode' query to 'Direct Query'

[D_PBI]

Hi all,

I've built a report which has a number of Power Query (PQ) queries. Some of the queries are quite complex (containing merges, appends, group bys, etc..).
I understand that the 'Import Mode' connection method allows full PQ capability but the 'Direct Query' method limits considerbly what you can do in PQ.

Originally, the PQ queries used the 'Blank Query' connection method. As I was connecting to MS Dyanmics, I was using a FetchXML script and placing it inside the 'Blank Query' connection. From there I was able to develop Applied Steps using PQ's full functionality. For this reason, I assume the FetchXML Blank Query was using the 'Import Mode' connection method. Note, I couldn't see any reference to 'Import Mode' or 'Direct Query' inside of the FetchXML script.

Since then I am sourcing the data from a MS Azure SQL Server. As such, I am now using the Azure SQL Server db connection and opting for the 'Direct Query' method. All I've needed to do is swap out for the FetchXML/Blank Query opening applied step for the Direct Query applied step connecting to the Azure db. The remaining applied steps (i.e. merges, group bys, etc..) are left untouched.
In doing this I was half expecting to receive an error but I didn't and all works. However, when I tried to applied an additional applied step, once I had converted the opening applied step to Azure, I wasn't able to - therefore I believe my understanding is right in that 'Direct Query' connect utilise certain PQ functionality. However, it would seem that any PQ functionality already in place can be used with 'Direct Query'.

My questions:
1) Has anyone else completed the same action in creating an 'Import Mode' type connection method, applying complex PQ functionality, and then swapping over to the 'Direct Query' method?   If yes, has it continued to work or am I likely to face issues and what issues are they?  I'm aware that if I need to apply further PQ steps then I may need to swap back to an 'Import Mode' connection, complete the steps, and then swap back to 'Direct Query'.

2) When comparing Azure SQL Server db 'Import Mode' connection string to the Azure SQL Server db 'Direct Query' I see no differences. They are the exact same. The only place when I saw the option to choose 'Import Mode' or 'Direct Query' was when choosing the Azure connection method and typing in the Server Name, Database Name and the optional SQL query. How do I determine if the Azure db connection string is 'Import Mode' or 'Direct Query' once it's been created (and therefore I hadn't be the person actually creating the connection/configuring the server/db/query details)?

Thanks in advance.

[D_PBI]

@v-jingzhang 

Thank you for your response.

Having checked the 'Storage mode' value for the report in question shows each table as 'Import'. So the attempt to switch all PQ queries from a 'Import Mode' connection to 'Direct Query' failed.
The odd thing is I never received, or I don't remember, a message explicity stating the chosen 'Direct Query' would change to a 'Import Mode' connection. It seemed like it did it implicity.
Through testing I did come across an indication (below). Does this mean that if I click on the button it will switch all my PQ queries over to 'Import Mode' even though the message is just appearing on one query?

Just in case anyone else finds this next bit interesting... I went on to try some tests. This is what I found.
1) Created a Azure SQL connection as 'Import Mode'. I didn't provide a SQL statement but instead just selected and entire table. There were no addition PQ Applied Steps. 'View Native Query' was viewable. The table was showing as 'Import' for the 'Storage mode' value in Desktop.
So this suggests that each time a data refresh takes place the processing will be completed on the Azure SQL server, but each report visual interaction will consume data from the cached model.

2) Created a Azure SQL connection as 'Direct Query'. I didn't provide a SQL statement but instead just selected and entire table. There were no addition PQ Applied Steps. 'View Native Query' was viewable. The table was showing as 'DirectQuery' for the 'Storage mode' value in Desktop.
So this suggests that each time a data refresh takes place the processing will be completed on the Azure SQL server, and each report visual interaction will too be referencing data on the Azure SQL server.

3) Created a Azure SQL connection as 'Direct Query'. In the SQL statement window I wrote a 'SELECT * FROM table'. There were no addition PQ Applied Steps. 'View Native Query' was NOT viewable. The table was showing as 'DirectQuery' for the 'Storage mode' value in Desktop.
So this suggests that each time a data refresh takes place the processing will be completed on the Azure SQL server, and each report visual interaction will too be referencing data on the Azure SQL server. This bit confuses me - if the PQ connection is 'Direct Query' and the PBI Desktop is stating 'DirectQuery' for the Storage mode' then why am I seeing the 'View native query' as greyed out. I thought if all was showing as 'Direct Query' then the 'View native query' should be present???

Can anyone explain why?

Thanks.

[v-jingzhang]

Hi @D_PBI 

Whether all applied steps in Import mode can still work in DirectQuery mode depends on whether this query can achieve query folding. For a DirectQuery storage mode table, the Power Query query must achieve query folding. If the query cannot achieve query folding in DirectQuery mode, it will require you to convert this query to Import mode. 

For relational DB data sources, you can right-click the last applied step in the Query Settings pane. If the View Native Query option is enabled (not greyed out), then the entire query can be folded. You could refer to this document to see which transformations can achieve or prevent query folding.

For the second question, you can switch to Model view in Power BI Desktop, select a table and expand Advanced tab in the Properties pane to see which mode the table is. 

Other references for query folding:

Query folding basics | Microsoft Docs

Query folding guidance in Power BI Desktop - Power BI | Microsoft Docs

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.