Power BI Switch from Import to Direct Query Mode

Power BI Switch from Import to Direct Query Mode

In this article, we will explain How to switch from Import to Direct Query Mode in Power BI.

Power BI Switch from Import to Direct Query Mode

You may like to Install and Configure Power BI Report Server On-Prem.


Power BI Switch from Import to Direct Query Mode


Import vs Direct Query Mode In Power BI

In Power BI Desktop, there is two connectivity mode when you connect to a data source:

  1. Import Mode. [Import copy of data]
  2. DirectQuery Mode. [Connect directly to the data source]
Power BI connectivity mode

You may be also interested to check Power BI: Edit Existing SQL Query


Import mode in Power BI

In Import Data Connectivity mode, you can

  • Imports or copies the data set into Power BI desktop,
  • It’s used to import the full data set again and get the new changes, you must refresh the data via schedule or refresh button.

Advantages of Import Mode in Power BI

  1. All Power BI features are available in Import Mode, like alternative login to the database connection, relationship filtering with “Both” directions …etc.
  2. Faster than DirectQuery Mode, due to the data loaded into the memory, the query calculated from loaded data.
  3. DAX expressions are fully supported.
  4. Single and Both directions in the relation filtering are available.

Import Mode Limitations in Power BI

  1. Depends on the imported data size, A lot of consumed memory and disk space
    • On your machine (during the implementation),
    • On the online/on-prem server (when it published).
  2. The Power BI file size can’t be bigger than 1 GB.
  3. You will get an error If the file size is bigger than 1 GB, in this case, you must have the Power BI Premium that allows having 50 GB file size.
  4. No recent data without a refresh.

Direct Query Connection Mode In Power BI

In the Direct Query Mode, the Power BI report connects directly to the data source to always view the current data, no data copies in Power BI file, just small metadata like tables, fields names will be stored.

Advantages of Direct Query Mode in Power BI

  1. Always use the current data.
  2. Scalable, No limitation for 1 GB dataset.
  3. Build reports with large datasets.

Direct Query Mode Limitations in Power BI

  1. Slower than the Import mode.
  2. Can’t use alternative database credentials.
  3. Can’t combine multiple data sources in Power Query. All must come from a single data source.
  4. No data tab, only report and relation tab.
  5. The “Single direction” is only available. Meanwhile, you can enable “Both direction” for DirectQuery in the preview features.
  6. Time capabilities are not available.
  7. The date columns like Day, Month and Year are not supported.
  8. The returned data to Power BI must be <= 1 million rows.
  9. Many DAX functions are not supported. Meanwhile, you can overcome this limit by Allowing Unrestricted Measure in DirectQuery Mode.

Allow unrestricted measure in Direct Query mode

You can allow unrestricted measure in DirectQuery mode to enable valid DAX functions for measures but that may lead to very slow queries.

  • File > Options and Settings > Options.
  • Click on “DirectQuery“.
Options and Settings In Power BI
  • Check “Allow Unrestricted Measure“.
Allow unrestricted measures in DirectQuery Mode in Power BI

Switch from Direct Query Mode to Import Mode in Power BI

Steps

  • First, make sure that you have at least Power BI Desktop May 2019 version.
  • Open Power BI file.
  • Look at the status bar on the right side.
  • Click on “Storage Mode: DirectQuery (Click to change)” as shown below.
Change from DirectQuery Mode to Import Mode in Power BI
  • Click on “Switch all tables to Import mode“.
Switch all tables to Import mode In Power BI

Note: When you switch from Import Mode to Direct Query Mode, you will not be able to switch back to Direct Query mode.

  • Wait a moment until the DirectQuery mode changed to Import mode.
Change all tables to import mode in Power BI

Switch from Import Mode to Direct Query Mode in Power BI

Due to many features in Import mode is not supported in the Direct Query mode, it’s not possible to switch from Import Mode to Direct Query Mode. However, you can go though the below mentioned workarounds.

Before going through these workaround, you should be aware of it is ONLY suitable if you have a SINGLE data source.

[Workaround 1] Switch from Import Mode to Direct Query Mode in Power BI

Steps

  • First, make sure that you have at least Power BI Desktop May 2019.
  • In the Home tab,> Click on “Edit Queries” > Select “Edit Queries“.
Edit Queries in Power BI
  • The “Power Query Editor” would be opened.
  • On the left side, you can find the “Queries” list.
  • Click on the query.
Queries in Power Query Editor In Power BI
  • In the right, you can find the “Query Settings” for the selected query.
  • Below the “Applies Steps“, Click on the Setting icon gear besides the “Source” to edit the “Query Source“.
Edit the Source in the Query Settings in Power BI
  • In the “Data connectivity mode“, Check “DirectQuery“.
Change from Import Mode to DirectQuery Mode in Power BI
  • Close and Apply.
Close and Apply in Power BI
  • Go back to Edit the Queries again.
Edit Queries in Power BI
  • Click on the source, you will note that, the DirectQuery option is selected.
  • You will also get this message that means the current mode is DirectQuery.

This step results in a query is not supported in DirectQuery Mode

This step results in a query is not supported in DirectQuery Mode

[Workaround 2] Switch from Import Mode to Direct Query Mode in Power BI

Steps

  • Again, Edit Queries.
Edit Queries in Power BI
  • Delete all Queries.
Delete Query in Power BI
  • Close and Apply.
Close and Apply in Power BI
  • Apply Query changes.
Apply Query Changes In Power BI
  • Go back to the report view, you will note that all visuals have been damaged.
something wrong with one or more fields in Power BI visual
  • Now, Click on “GetData“, select “SQL Server“.
  • Provide the server name and database.
  • Check the “DirectQuery” mode.
Switch from Import Mode to DirectQuery Mode in Power BI
  • Select the same tables that you have earlier selected in the import mode.
Select tables in Power BI
  • Load the data, wait a moment until the data refreshed.
  • If the loaded data has the same tables and fields name, you will note that the visuals in your report have been automatically fixed.
Fix visuals in Power BI

Conclusion

In this article, we have explained:

Applies To
  • Power BI Desktop.
  • Power BI Service.
  • Power BI Report Server.
You might also like to read
Download

Check PBIX samples with data sources repository on GitHub, and Please, don’t forget to Follow Me to get the latest updates.

Have a Question?

If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.

2 thoughts on “Power BI Switch from Import to Direct Query Mode”

  1. Gabriel Watah

    What’s up to every one, as I am in fact eager of reading this web site’s post to be updated regularly. It includes nice stuff.

Leave a Reply