Hi,
I have an Excel file (File 1) on SharePoint (to be used among SharePoint users) which needs to look up a table (Table 1) with data that come from SQL server (Database). The Table 1 needs to be refreshed daily. However, the access to this Database is restricted to very few people (including me). Therefore, I cannot connect Power Query directly from File 1 to Database, since not all SharePoint users are allowed to access Database.
I am thinking of the following overall approach but I am still not sure how to get it done:
1. Create an Excel file (File 2) with the query results (Table 2) on my OneDrive. File 2 thus has access to Database.
2. Refresh Table 2.
3. Open access of File 2 to File 1.
4. Refresh Table 1 from Table 2 via Power Query.
5. Have Power Automate to have #2 and #4 daily.
If the above approach is feasible, can you please advise what is the flow required for #5?
Thank you.
