Moving Azure SQL database away from personal gateway to allow refresh

Hi harryws ,
According to your description, you want to refresh the plant data during non-business hours while you have migrated the SQL database to Azure, but the refresh is not successful.
First of all, Azure SQL Database supports DirectQuery, which allows Power BI to query the database directly without importing the data, thus keeping the data up-to-date in real-time. You can check out this document for details on connecting Power BI to Azure SQL Database using DirectQuery.
Second, you mentioned that your computer is turned off during non-working hours. However, Excel spreadsheets still require a gateway to refresh if the data is not hosted in a cloud service such as OneDrive or SharePoint. You might consider moving the Excel data to OneDrive or SharePoint.Power BI can connect to Excel files stored in OneDrive or SharePoint Online, thus allowing scheduled refreshes without the need for a personal gateway. You can refer to this documentation.
Finally, although DirectQuery has some limitations, you can still create calculated columns and metrics. These limitations are primarily related to the complexity and performance impact of the calculation.

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly