Forum Discussion
From SQL to PowerBI : intermediary file
Hi Anonymous,
Thanks for your follow-up. I understand that copying almost 130 queries manually is not an easy task, so let's find some other ways to do it:
As you wanted to try storing data in Excel, let’s check if it can work as a Data Source. Follow these steps to create an ecxel file for your data.
* First Create an Excel file with one table per sheet.
* Then Load data from SQL using Power Query in Excel.
* Now you have to save the file in OneDrive or SharePoint for accessibility.
* In Power BI, connect your reports to this Excel file.
* You have to use Power Automate to refresh the Excel file twice a day.
Another option is using Power Automate for Automated Refresh. Instead of Excel, keep your Donnees dataset and use Power Automate to refresh it twice a day. Your three Power BI reports connected to it via Live Connection.
You can also use Power BI Dataflows stored in OneDrive or SharePoint. First create a Dataflow in Power BI Service to centralize SQL queries then connect all three reports directly to the Dataflow instead of SQL. Using this you don't need to manage multiple copies of queries.
I hope this solves your current requirements. 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.
I'd love to find out how to properly use Power Automate to refresh the Excel file twice a day. Quoting my own very first post in this conversation,
"If i make this file an Excel file, i tried many things, but i don't find how to properly get it updated twice a day without having a dirty solution like the server opening this Excel and a macro refreshing it (which will not happen). I found a "solution" in power automate, but it doesn't work (the script gets executed, supposedly, the data doesn't get refreshed, while power automate says the flow worked fine.
I also found this, https://damobird365.com/export-power-bi-to-excel-with-power-automate/#Populate-an-Excel-file-with-Gr... , but i don't fully understand everything involved, which makes it a nightmare to reproduce, and even more to maintain, if it really is a solution, which i'm not certain of."
I followed several tutorials on how to refresh the Excel file by Power Automate, none worked, or i failed, i don't know.
I'm going to retry later on today and show images of the problems i have while trying to setup Power Automate. If in the meanwhile anyone has a method they KNOW to work, i'm all ready to follow it.
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 TeamIf 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.