Import files once, never load them again

[Anonymous]

I've made a dashboard that uses a combination of data from an azure db and files that I imported once from my virtual machine. 

I'm now unable to schedule a refresh because "data source credentials" is greyed out on the Power BI admin website. It looks like it wants me to use an on-premise gateway to refresh data (because there are a few file connections in the dataset). 

However, I don't want to use the on-premise gateway. I don't even want to refresh the data from the files. They were a one time upload that I will NEVER change again.

In Power BI desktop/powerquery I've already deselected the relevant option ("include in report refresh"), but it still won't allow me to enter data source credentials.

So what should I do? I can not deselect "enable in load" because it will remove the table, which obviously I don't want. Is it possible to treat the files as a one time data import, without requiring active connections to the original files?

[v-yuezhe-msft · Accepted solution]

@Anonymous,

The "include in report refresh" option only has effect on Power BI Desktop report, it will not affect the refresh in Power BI Service. 

When you combine online data source(Azure SQL database) and on-premises data source(Excel file) in a single dataset, gateway is required to refresh the dataset, and it is not possible to refresh specific data source in this single dataset. If you don't want to use gateway and want to set schedule refresh, you can upload Excel file to OneDrive,  then connect to the Excel in Power BI Desktop.

Regards,
Lydia

[Anonymous]

Thanks Lydia, that worked!

I found it a little tricky to get it done, but to other who are reading this and have the same problem:

1. Upload your files to your workspace files in power bi (go to power bi web -> workspace -> files and upload them there)

2. In Power BI Desktop, change the source for your flat file queries to the web URL (find the web URL here)

3. Apply changes, re-publish your dashboard

4. Finally, in settings, make sure you use OAuth to establish a connection to each of the files individually. Once done you should be able to set a refresh schedule