In some cases, it may be necessary to separate a Power BI report from its dataset. For example, you may want to have multiple reports based on a single dataset, or you may have upgraded to Analysis Services. Currently, Power BI Desktop does not provide a simple way of switching from imported data to Live Connection or DirectQuery, but there are some workarounds.
Switching to Live Connection
Several people have already blogged about changing the connectivity mode from Import to Live Connection:
- Erik Svensen described how you can switch from imported data to Power BI service or Azure Analysis Services by exploiting the .zip nature of .pbix files.
- Kasper de Jonge shared a way to re-bind any Power BI report to SSAS (or any Power BI dataset) by using Power BI REST API.
- Rui Romano has shown the simplest way of converting a Power BI Desktop file from Import to Live Connection. I’ve heard Peter Myers sharing the same technique last year as well.
All these techniques rely on the following rule:
Every field you use anywhere in your report must exist with the same name in the same place in your new dataset.
For example, if you have a .pbix file with imported data, and you are using the Sales Amount measure from the Sales table in one of your visuals, then a measure with the same name must exist in the Sales table in the new dataset. If your new dataset contains a column called Sales Amount, then the visual that used the measure will display an error.
On the other hand, if you have more fields in your new dataset, that’s okay. Also, if your new dataset does not have some of the fields from the old dataset, but they are not used anywhere in the report, that’s fine too. This is because Power BI visuals internally rely on field names and not the datasets being identical.
You can also switch from DirectQuery to Live Connection by using one of the techniques above.
Switching to DirectQuery
Here’s something I discovered myself: by using one of the techniques above, you can go from imported data to DirectQuery! I still hear some people saying that it is not possible to switch from imported data to DirectQuery, so I decided to set the record straight.
For instance, you can delete all your queries by following the steps in Rui’s blog post, and then connect to your database and choose DirectQuery instead of Import. The tricky part is getting all the tables and columns with the exact same names of course.
Therefore, if you perform complex transformations in your queries, it may be a good idea to save all queries in a text file before deleting them. You can follow these steps to do so:
- Open Power Query Editor by clicking Edit Queries.
- Make sure the top query is selected.
- Hold the Shift key and click on the bottom query — this will select all queries.
- Press Ctrl + C.
- Open your favorite text editor (like Notepad).
- Press Ctrl + V.
You will then see something like this:
Note how every query is preceded by its name.
Once you delete all your queries, you will need to have as many queries as you originally had (assuming you used fields from all of them). Unfortunately, you will not be able to use the Blank Query source (if you try that, you will be prompted to switch to Import mode). However, you can still make use of your text file with queries:
- Connect to any view or table from your data source by using DirectQuery and click Edit instead of Load.
- Duplicate your query as many times as you need.
- Rename all queries to the correct names. You can get the exact names from the text file you created before.
- In each query, open Advanced Editor and replace the code by copying the relevant query from the text file.
Even with this technique, all the limitations of DirectQuery still remain in place. You will probably need to do some extra modeling (create measures and relationships, etc.), but depending on your situation, it may involve less effort compared to rebuilding your reports completely.