Forum Discussion
Ignore Data Refresh for local datasets
Hi Community.
I have a PowerBi dataset with connections to a SQL Azure instance and a local spreadsheet. Within the query properties for the spreadsheet i've deselected the "Include in report refresh".
In the PowerBi service I want to set the Data source credentials so I can schedule a refresh of the SQL Azure data. Due to the local excel spreadsheet, I cannot set this as PowerBi service wants a gateway connection setting which I don't want.
How do I just set the SQL Azure datasets to refresh with a schedule in PowerBI service whilst there is a data connection with excel in the one document?
Hi Danch89,
You cannot perform a refresh while some connection details are missing (even using premium features to invoke a single table refresh).
If the excel data is static, you should insert it into the model as hardcoded values using Power Query:
The other option will be to move the excel data to a staging layer from where it will be able to be refreshed without a data gateway. For example:
1. Upload it to your Azure Sql DB.
2. Upload it, as CSV, to an Azure storage account (you can also use Power BI data flows- but this will require a one time use of a data gateway).
You can also do stuff like uploading a separate power bi dataset with the excel data, then connect to it in various ways, but this is really going too far...
3 Replies
Hi Danch89,
You cannot perform a refresh while some connection details are missing (even using premium features to invoke a single table refresh).
If the excel data is static, you should insert it into the model as hardcoded values using Power Query:
The other option will be to move the excel data to a staging layer from where it will be able to be refreshed without a data gateway. For example:
1. Upload it to your Azure Sql DB.
2. Upload it, as CSV, to an Azure storage account (you can also use Power BI data flows- but this will require a one time use of a data gateway).
You can also do stuff like uploading a separate power bi dataset with the excel data, then connect to it in various ways, but this is really going too far...
