Ignore Data Refresh for local datasets

[Danch89]

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?

[SpartaBI · Accepted solution]

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

[Danch89]

Thanks - I copy pasted as a table which fixed the solution of refresh in the PowerBI service.

[SpartaBI]

@Danch89 happy to hear it was solved.
Please don't forget to mark that previous comment as a solution to help other community members find it more easily.