Product:
Microsoft Power BI
Microsoft Windows 2016 server

Issue:
You have a working Dynamic SQL query, but when you schedule it, it stop working.

Suggested solution:
Rebuild your query to not use Dynamic SQL, to make it possible to schedule it in PowerBi.

See if you can change to use fewer different data sources.

More Information:

https://docs.microsoft.com/en-us/power-query/dataprivacyfirewall

https://medium.datadriveninvestor.com/setting-a-scheduled-refresh-on-a-dynamic-data-source-in-power-bi-409ccec7337b

https://community.powerbi.com/t5/Service/Dynamic-SQL-through-ODBC-is-breaking-refresh-capability/m-p/276408

https://powerbitalks.com/2020/06/refresh-dataset-using-button-powerbi.html

https://docs.microsoft.com/en-us/power-bi/connect-data/refresh-data