Wednesday, May 17, 2017

Power BI - Convert Direct-Query to Import

Power BI supports three types of connectivity: Import, DirectQuery and Live Connection. We generally use DirectQuery if the dataset is large and use Live Connection for models that have been created with SSAS. Once the Power BI file is created with DirectQuery, can we change it to Import?

This is something I was asked yesterday and my answer was Yes. We can the mode used, and get the data imported than referencing data via metadata.

How to convert DirectQuery to Import?

Let's create a file with AdventureWorksDW2014 with DirectQuery mode;

Once it is created, we can see the only Relationship View is visible as no data is imported.

Let's just create a visual and save the file.

Let me close the file and open it again. Once opened it again, if the file has been created using DirectQuery mode, I will be seeing a button right-bottom called DirectQuery: Enabled (click to change).

I can click on it and change the mode. This is what I get when it is clicked.

Let me select Import and continue. Now I see data imported to my Power BI file.


Andrés Romero said...

And, what about the inverse operation?, change from Import to Direct query, is that possible?

Dinesh Priyankara said...

Unfortunately, it is not possible. I think that the reason for that is, ability to transform (or modify) extracted data set using Query Editor. If it allows to change Import to Direct-Query, then PBI needs apply all modifications done (otherwise, visuals will not work) to direc queries, which is not possible.

Suhas Madappa said...

Is there a way to enable hourly refresh on Import query ?

Dinesh Priyankara said...

Hi Suhas,

I believe that you are talking about importing data from a file and then get it refreshed hourly. It is possible. You need to configure the gateway (you can download and install) and set the schedule using Power BI online service.

You cannot create a schedule using Power BI Desktop.