Convert import mode to direct query in Power BI

Table of Contents

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.

Import mode connectivity

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. 

PowerBI Dashboard using import mode

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.

Direct query data connectivity
direct query .pbix file with data

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.

Command prompt
Changing file 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

2 thoughts on “Convert import mode to direct query in Power BI”

  1. 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

Leave a Comment

Your email address will not be published. Required fields are marked *