Convert storage mode from direct query to import, data source as power bi datasets

[Anonymous]

Hi,

Is there a way to convert the storage mode from direct query to import mode without having to delete and redo the entire model?

I have a report where I pull multiple power bi datasets, in which I apply additional tables and modelling to link these datasets together. The report is loading very slowly (especially when creating calculated columns and measures), so I want to convert the storage mode into import to improve the load time. However, the storage mode dropdown is greyed out:

.

Is there a way I can change the storage mode? Thank you!

[ropebender]

These solutions no longer work.

I am not gifen an option to change in the Model View

The Staus Bar does not have the "Change" option

and I even tried the trick of creating a new calculated column.  It let me create one.

I also added a new file and made it Import mode, and I can go to the Tables view and that is the only table that shows up.  The other tables show "Direct Query" on a gray background, and there is no way I have found to change it.

Anyone have any additional tricks?

Claude Van Horn

[brianjwilson]

Try creating a calculated table from the origial direct query table, not just a calculated column. And then use that calculated table for all relationships, visuals, etc. That calculated table will be in import mode.

[BugmanJ]

If you do this, you cant create a scheduled refresh in PowerBI service sadly

[ropebender]

I'm not positive how I can create a calculated table from the Direct Import Table, but even if I could, wouldn't I still have to wait for the Direct Query table to search and load every time I change a parameter?  I'm trying to eliminate the delays caused by the Direct Query.

Am I missing something?

PS.  I figured out how to create a new table from the Direct Import query, but there's not enough resources to do that.

So I'm still looking 🙂

Thanks for the help!  

Claude Van Horn

[brianjwilson]

I had this issue and was able to work around it. I believe my situation was similar to the original post in that I had a PBIX desktop report with several imported data tables from various sources. I then wanted to get data from a published Power BI dataset and be able to create relationships between that data and the other data within my report. To do this I 1) created a calculated table for each of the Power BI dataset tables I wanted, removing extra columns I wasn't interested in using the SELECTCOLUMNS function, naming each column something unique by simply prefixing the column name with the name of the original table - this is required for a future step to work, 2) created relationships between those newly calculated tables, 3) created a calculated table using NATURALINNERJOIN to combine all of those tables together into one (which will use the relationships for the join conditions and requires unique column names to work in DAX). Now I have an imported table with all of the data from the Power BI dataset that I was interested in that can be used across the other data that has been imported. Hope this helps for you.

[Anonymous]

Hi @pratyashasamal , thank you for your reply!

I want to convert from direct query mode to import mode, which you mentioned is possible. Could you advice on how to do so? 

[pratyashasamal · Accepted solution]

Hi @Anonymous ,
You can go to Power BI Desktop, go to Model view-->Advanced and change the storage mode from DirectQuery to Import .

Similar issue is also resolved in this particular community post . You also follow that post :
https://community.powerbi.com/t5/Desktop/How-to-change-direct-query-to-import-query/m-p/100996

Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[mikemagill]

Has anyone identified a solution to the original poster's problem, which I too am experiencing?

I am connected to two Semantic Models and then am using calculated tables to further manipulate the data.  I can publish the report but it warns me that it will not refresh.

I thought by changing to import mode I would not have this problem but there is no option.

Any help would be appreciated.

[Anonymous]

Hi @pratyashasamal , Thank you for your reply.

The option to change storage mode is greyed out for me (please see my original post for a screenshot of what I mean.) Do you know any possible reasons that can cause this and if there are any ways to change this? Thank you!

[pratyashasamal]

Hi @Anonymous ,
When Power BI Desktop detects an incompatible configuration, it may disallow the change .
Can you please let me know what is the datasource ?
Thanks ,
Pratyasha Samal

[Anonymous]

Hi @pratyashasamal ,

The data sources I am trying to switch to import mode are power bi datasets.

Thank you.

[pratyashasamal]

Hi @Anonymous ,
The datasource may be  excel , sharepoint , web , etc . What is it ?
Thanks ,
Pratyasha 

[pratyashasamal]

Hi @Anonymous , 
Another way can be :-

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

Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Anonymous]

Hi @pratyashasamal ,

My storage mode is not clickable, and doesn't have the "(Click to change)" option.

Please note that the report contains other tables from other sources, such as excel files from sharepoint folder. These are already in import mode - hence "Storage Mode: Mixed" in the screenshot. 

[pratyashasamal]

Hi @Anonymous ,

Can you see something like this in Query Editor, Change the Connection Settings to Import Mode.
Thanks ,
Pratyasha Samal
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[Anonymous]

Hi @pratyashasamal ,

The direct queries don't show up in the query editor, so there is no way to change it this way either.

Thank you!

[dg123456789]

I'm in the exact same spot. When you pull in a semantic model as a datasource you do not have the ability to view or edit this table in Power Query, which is why we need to be able to switch it to Import. 

One workaround I've found is to use the SQL Server Analysis Services and then SUMMARIZECOLUMNS but then you will not be able to schedule automatic refreshes in the powerbi service. 

[pratyashasamal]

Hi @Anonymous ,

As far as I know, it is not possible to convert import mode to direct query mode, But direct query mode can be converted to import mode

If you really need to change it to direct query,

my suggestion is to create a new one using direct query then copy your applied steps from your previous report.

It will be quite time consuming but it will achieve your requirement.
Thanks ,
Pratyasha Samal 
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

[leathard]

Wow, how did this question get marked as resolved?  Reading through this thread I can only tell that so far you haven't understood the original question, which others are asking too, and I am experiencing. 

The following Microsoft articles describe that we should be able to change the import mode for semantic model tables and I certainly can't because the option is greyed out:

<https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-storage-mode> 

<https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-composite-models> 

[brianjwilson]

Agree. I had the same issue and posted the solution I came up with, but it got placed in the middle of the thread for some reason. Here's what I did and it worked for me. And I've done it on two different reports and it worked for both. And on the second report, it was simpler because I was only pulling in one direct query table from another Power BI dataset. In that case, all you have to do is create a calculated table in your report that selects all the columns from that direct query table, and then build all of your relationships and visuals off of your calculated table instead of the direct query one. That worked fine for me.

I had this issue and was able to work around it. I believe my situation was similar to the original post in that I had a PBIX desktop report with several imported data tables from various sources. I then wanted to get data from a published Power BI dataset and be able to create relationships between that data and the other data within my report. To do this I 1) created a calculated table for each of the Power BI dataset tables I wanted, removing extra columns I wasn't interested in using the SELECTCOLUMNS function, naming each column something unique by simply prefixing the column name with the name of the original table - this is required for a future step to work, 2) created relationships between those newly calculated tables, 3) created a calculated table using NATURALINNERJOIN to combine all of those tables together into one (which will use the relationships for the join conditions and requires unique column names to work in DAX). Now I have an imported table with all of the data from the Power BI dataset that I was interested in that can be used across the other data that has been imported. Hope this helps for you.