Workaround to convert Pbix file from Import to Direct Query mode

[Anonymous]

Hi, 

Is there an easy way to conver pbix file create in import mode to direct query mode.  I understand, Copy paste visuals from import pbix file to direct query as long as the meta data matches, works  fine but i dont want to go this route. 

Bascially, I am looking to understand , when get the data from source we set the Import/Direct Query toggle button, what part in M Query sets the source to be import /direct query that I can change configuration . 

What if we save the Pbix file as template with connection parameters?

The new XMLA Read/Write features , can it be helpful in this context?

regards

[Anonymous]

Finally the way I handled it was , disabled the table queries in transfromation layer.  Added new queries with direct query with same table and column names. deleted the import tables and re-did the relationships.

It worked with minor tweaks. 

[DataZoe]

I tried to do this recently, but abandoned it because many of the DAX expressions I had used in building the import model were not compatible with direct query model. 

If I were to go about it now, I would create the model as direct query to start with, and keep that as the model I modify in future. Then when I'm done, I would save as a different name and switch it from direct query to import (as that is simply click and be done). So, I would have 2 models, one import and one direct query that I would publish.  I'd always have to make changes in the direct query model save that, then change to import and override the import model pbix save.

Then I could create a new pbix as the report with the all the visuals and connect via power bi datasets to either model (import or direct query) and use them interchangeably.

[Anonymous]

Thanks, I do see the beniefit of isloation. In this specific case, it is a more of a migration work for to fix. 

regards

[DataZoe]

@Anonymous In that case, I was able to pull the bim file out using tabular editor, but I think you would still need to create the data sources in a new bim as direct query, then bring the tables in from the import bim defintions. 

If the model is not too big, it's not too bad to just recreate. I only had 10 or so tables, so I had created the direct query data source in a new PBIX,  then I used that with the mscripts I had for the other tables to recreate them. I then re-added the measures/columns. That's when I realized I had so many DAX issues that I didn't want to fix :). 

[v-eachen-msft · Accepted solution]

Hi @Anonymous ,

You could refer to this blog:

https://xxlbi.com/blog/switching-from-imported-data-to-directquery-or-live-connection-in-power-bi/

Note: This link contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

[Pragati11]

HI @Anonymous ,

Currenlty, there is no way to switch back to DIRECTQUERY mode from IMPORT mode.

Refer the following similar thread:

https://community.powerbi.com/t5/Desktop/Convert-Import-to-Direct-Query/m-p/293361

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati