As the title says how do you really do it? Because you can go from DirectQuery <> Dual > Import but you can't go from Import to Dual or DQ
Look at this model, here I have all table in DQ:
I set Customer to Import and Date to Dual, from this point onwards Customer's storage can't be changed to Dual/DQ but Date can go to Import or DQ
Customer:
Date:
So how do we make Customer or other tables in Import mode go back to either Dual or DQ? In comes the PBIP file format, the .PBIX format is restrictive in the types of changes that you can make to the data model. However, under the hood PBI desktop modifies the Model.bim file which contains the metadata of the whole data model, if we can modify this we can change storage.
Point to note here is you are allowed to change the storage type in PBIX file using Tabular Editor however it is isn't reliable as it just goes into the loop of warning in yellow bar.
After saving the changes if I try to refresh it accepts the changes however the yellow bar doesn't disappear
I saved the file and reoppend it and I can see the data in the Products table however I find it is a bit unreliable way.
For example if I try to swith Customer from Import to DQ it just doesn't work at all, it refreshes itself and loads the data again.
Solution
A foolproof working solution is to save the project as PBIP and then use Tabular Editor to modify the model.bim file.
As of April 2024 you need to make sure you're not using the TMDL format for the metadata and instead use the original JSON format otherwise PBI file won't compile and open.
Keep TMDL option unchecked.
Wherever you save the PBIP file/folder you will find this model.bim file, this is what you need to edit to easily switch storage mode
This is what we need to change, you can edit the text file manually but I prefer Tabular Editor.
You can open the PBIP project either using From Folder option of Tabular Editor or Using the File option you can open the model.bim file
Then for each table of which you want to change the Storage type, go to that table > paritition > whatever is the name of the partition, select it and using the properties pane change storage.
Customer: DirectQuery
Sales: Import
Save the changes to the file and then open the Power BI Project file and everything will work as expected without any issues or warnings.
Please note there is a bug in the April 2024 version of PBI Desktop due to which the limited/weak relationship indicators aren't shown that's why you see strong relationship between Customer & Sales even though both are in different storage modes, same for Product table.
Just so you know using Tabular Editor you can also switch the storage modes in Power BI Service as well, which helps specially when creating hybrid models.
If you wish to quickly switch storages of all tables you can use C# script in TE3, here is an example:
foreach (Table t in Model.Tables)
{
foreach (Partition p in t.Partitions)
{
p.Mode = ModeType.DirectQuery;
}
}