Introduction
In this article, I am going to explain how to convert the data connectivity from import mode to direct query mode. I got this trick in the powerbi community from a superuser. I applied this method to the powerbi dashboard where visual interactions are too complicated and it works fine.
The condition needs to be satisfied
1. Column names and table names in import mode should match with the direct query mode.
2. Get data needs to be done in a new file with direct query mode.
3. Measures and calculated column needs to be created after the conversion of import to direct mode.
If condition 1 fails also user can change the mode to direct query by changing the column name or table name in the direct query file.
Power BI report by Import mode:
Before starting the conversion, I will explain the dashboard creation using import mode data connectivity. Import the data using the get data menu in the Power BI desktop.
Once the data is loaded, I created a sample dashboard to explain this. Here the left two charts are without measure and right one having measure.
The conversion of import mode to direct query of this file is explained step by step in detail here.
Step 1: Load the data in direct query mode
When you need to convert the import mode to a direct query, the developer needs to load all the tables in an empty pbix file in direct query mode.
The tables are successfully loaded into Power BI in direct query mode.
Step 2: Change the file format
To replace the data model we need to convert the import and direct query file to zip format. Here I used the command prompt to change the format.
The files are successfully converted to zip format.
Step 3:Copy the Data model from direct to import file
If you open the import mode zip file, you can find DataMashup and DataModel.
Delete the DataMashup and DataModel files from the zip folder in the import file.
If you open the direct query zip file, you will find the DataMashup and DataModel files in the folder.
Copy the DataMashup and DataModel files to the import mode zip folder.
Convert the import mode zip file to the pbix format and rename the file.
Step 4:Create/Copy measures and calculated columns
Once the file conversion is done, you will be able to see the direct query file in the folder.
Now open the new direct mode pbix file and the output will be like that.
The right side chart uses a measure column as input. Do the data refresh and create the measure. In the end, the original output will be obtained as below.
For copying measure checkout this article
References:
This article references a source link. click here
Another method of converting import to direct query mode. click here
Save as PDF
2 thoughts on “Convert import mode to direct query in Power BI”
So you still have to create all your measures again in the direct query mode?
In that case this doesn’t prove to be very useful
Check out this for copy measure https://datacaffee.com/how-to-copy-a-measure-between-two-reports-in-power-bi/