Schedule refresh and get data into Flow to loop and send notifications few times a week without "On premise data gateway
Objective: Schedule refresh and get data into Flow to loop and send notifications few times a week without "On premise data gateway" Any ideas how to get data refreshed and feed into flow to loop through.
Limitations: -On premise data gateway is not allowed -Don’t have premium license -Have "On premise Power bi server" that i can publish a report and have refresh scheduled -Excel slq/power query can not be refreshed from flow -I have my queries with output ready in power query and sql -I have set up a Flow process that can loop and send emails from static excel data, as i can not refresh excel. Please advise.
We may need some further clarity on this one. Power Automate itself doesn't really refresh data sources, although some connectors can request it. Flows can definitely be put to a schedule but it sounds like you're already familiar with that.
If your data source is in an on-premises instance of SQL, you have to use an on-premises gateway for that. The SQL connectors in Power Automate are also Premium, requiring an appropriate license. This option doesn't seem viable based on your limitations.
You can use an Azure SQL instance, removing the need for a gateway but you would still require a Premium license for the appropriate connectors.
It already looks like you've dug into the Excel and Power Query possibilities and what you've noted tracks with common experience. You may want to post in the relevant forums for any further questions there but ultimately, a connector of some kind has to request it. PowerApps may work better in this regard.
These connectors are still in Preview but many people have success with them. You can also request a refresh of a Power BI dataset through these connectors. Given your limitations and already built Power BI reporting, this may be the way to go.