Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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...
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...
Thanks - I copy pasted as a table which fixed the solution of refresh in the PowerBI service.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.