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.
Hi @NercyCrab :
It looks like your connection to the SQL Sever database is not working.
You can click on the "..." in left and create a new connection.
Best Regards,
Bof
Thank you very much. This seems an easy and elegant solution.
However, I am getting an error message "connection not found" when I try to execute a SQL Server query in PA. Any advice?
Hi @NercyCrab :
You can create a scheduled cloud flow, extract the data in the database every day, and then use the acquired data to create a CSV file and replace it with the original excel file in SharePoint.
Best Regards,
Bof