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.

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]

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

• Check “Allow Unrestricted Measure“.

---

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.

• Click on “Switch all tables to Import mode“.

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.

---

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

• The “Power Query Editor” would be opened.
• On the left side, you can find the “Queries” list.
• Click on the query.

• 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“.

• In the “Data connectivity mode“, Check “DirectQuery“.

• Close and Apply.

• Go back to Edit the Queries again.

• 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

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

Steps

• Again, Edit Queries.

• Delete all Queries.

• Close and Apply.

• Apply Query changes.

• Go back to the report view, you will note that all visuals have been damaged.

• Now, Click on “GetData“, select “SQL Server“.
• Provide the server name and database.
• Check the “DirectQuery” mode.

• Select the same tables that you have earlier selected in the import mode.

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

---

Conclusion

In this article, we have explained:

• Import vs DirectQuery mode In Power BI.
• Import Data Connectivity mode in Power BI.
  • Advantages of Import Mode in Power BI.
  • Import Mode Limitations in Power BI.
• DirectQuery Connection Mode In Power BI.
  • Advantages of DirectQuery Mode in Power BI.
  • DirectQuery Mode Limitations in Power BI.
  • Allow unrestricted measures in DirectQuery mode.
• Change from DirectQuery Mode to Import Mode in Power BI.
• Change from Import Mode to DirectQuery Mode in Power BI.

Applies To

• Power BI Desktop.
• Power BI Service.
• Power BI Report Server.

You might also like to read

• Install and Configure Power BI Report Server On-Prem.
• Missing Save To Power BI Report Server In Power BI Desktop.
• Power BI: We couldn’t connect to the Analysis Services server. Make sure you’ve entered the connection string correctly.
• Calculate the Rows Count, Ignoring Filters In Power BI.

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.