How to change the Storage Mode from Import to DirectQuery

[Flo1]

Hi,

in Power BI Desktop for any table it is easy to switch the Storage Mode from DirectQuery to Import (by changing this property in the property pane in Power BI Desktop). Switching back from Import to DirectQuery is not possible in general.

There are a lot of posts and workarounds out there (like this one) how you can achieve changing the Storage Mode from Import to DirectQuery.

This message presents an alternative way to switch the Storage Mode from Import to DirectQuery, so you don't have to delete and reconnect your tables in your report (especially if you have a lot of tables inside your model).

But please notice: if you are pretty far in your report development, it cloud be you have used transformations, operations or DAX-Functions which requires imported data. After changing Storage Mode maybe some mechanisms of your report doesn't work anymore. That's one reason why changing the Storage from Import to DirectQuery is disabled.

To change the Storage Mode from Import to DirectQuery do the following steps:

1. Save your report as *.pbip-file in a location where you can find it easily (let's say the name of the report is MyReport.pbip).
2. Navigate to your location where you have saved your report and open the MyReport.Dataset folder.
3. Inside of this folder there is a file named model.bim - open it with an editor you want (like Visual Studio Code or something)
4. Inside of this file there are saved metadata of your data model, so you can find here also the property for the Storage Mode.
5. Search for the string "mode": "import" and replace it by "mode": "directQuery" - do this for every table element in the file and save it. The editor provdide "replace all" features.
6. In the MyReport.Dataset folder there is another folder named .pbi - inside of it there is a file named cache.abf -> delete this file (it stores the actual imported data) or at least remove it from this location and keep it anywhere else as backup.
7. Now you can (re)open your *.pbip-file. The tables should be in Storage Mode DirectQuery now.
8. You can refresh your data by clicking on the refresh button. After that the file cache.abf should be recreated and stores at least some metadata and cached data.

I hope this is helpful to you.

[foodd · Accepted solution]

Hello @Flo1 , thank you for your information post on "How to change the Storage Mode from Import to DirectQuery".