Forum Discussion
From SQL to PowerBI : intermediary file
Hi Anonymous,
As you already tried using Power Automate, please consider few points and try following the mentioned steps again.
Please make sure Excel file must be stored on OneDrive or SharePoint as Power Automate cannot directly refresh an Excel file stored locally on a server. Also make sure that Power Query is used to pull data from SQL Server into Excel.
As for the steps to create the Power Automate Flow please follow:
(I assume you have already prepared the data in Excel and stored it in OneDrive or SharePoint)
* First go to Power Automate then create a Scheduled Cloud Flow for every 12 hours.
* Now add "Run script on Excel file" action and select your file. Use this Office Script
function main(workbook:ExcelScript.Workbook) {
workbook.refreshAllDataConnections();
}
* Now add "Delay" action and Set 5 minutes and wait for refresh.
* Then add "Update File Properties" action and select the same Excel file to save changes.
Now you can save & test the Flow.
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Hammad.
Community Support Team
If this post helps then please mark it as a solution, so that other members find it more quickly.
Thank you.
Anonymous
Hello,
thank you for your answer.
I will be able to test this in 2 weeks and a half. It is part of my job, so you can count on me to answer then here about if it worked or not.
I thank you for your help on this matter.
Best Regards.
Hi Anonymous,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution so that other community members can find it easily.
Thank you.Hi Anonymous,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hello,
i've only started working again today. I have important matter to work on the next days, but i will have to look into this soon. As i stated before, it will be about 2 weeks and a half after my previous post mentionning this, the 14-03-2025.
As always, you can count on me to come back here and give news about this problem, may it be a solution or another problem.
I can already tell you, when seeing the issues i had with Power Automate and your answer, that this will probably not help with the issues i had, because : yes, i had Power Query fetching data from a SQL base into Excel, yes the Excel script was working in itself, and yes the Power Automate was marking the task as a success, except the task itself wasn't done : refreshing the file with the database.
I will be able to post in details in some days.
Thank you for your concern on my matter.