How to automate daily tasks of executing SQL Store Procedure & save the results to Excel file?

[qjr]

I am new to Power Query and Power BI, I would like to know if there is a way to automate a daily report.

My daily task is to send out Excel report with data from 5 SQL Stored Procedures. The current steps are:

1. Open the Excel template file, and save it to a new file name with today's date.
2. This Excel file contains five tabs, each tab represents the sane column layout of the five stored procedures. Each tab only contains two rows, 1st row is the formula to calculate sub-total on specific columns based on the data produced, and the 2nd row contains the formatted and colour-coded header name.
3. In SQL Server, I execute the 5 Stored Procedure separately.
4. When the Stored Procedures were executed, I copy all the results and pasted them in the corresponding tab inside the Excel on row 3.
5. Repeat the same process for the next four resultsets.
6. Save the Excel file and email it to the appropriate department.

Is there a way to automate this process using Power Query or a combination of Power Query and Python?

Thank you in advance for your help.

Qjr

[qjr]

Thank you,

Unfortunately, we don't have Power BI at office right now, I am trying to use Open OpenRowSet() to export to existging Excel file with header template within the SQL Store Procedure, I can schedule this script and another schedule to run the Batch file to copy the updated Excel file to share folder. I am trying this method at this point.

[PC2790]

Hello @qjr ,

As per your description of the tasks, it is very much possible to do it in Power BI.

You can connect your Power BI to the SQL from where the data is being fetched and run the scripts within power BI itself.

Then you can work on transforming the data into the desired format, modelling the data and finally visualising the data as per the end users.

The report can be published onto the cloud(Power BI service) and with the proper access given, it can be viewed by the end users anytime of the day.

It calso can be refreshed automatically if the refreshes are setup, without any intervention from your side.