One paginated report -1 dataset - export 2 tables to one Excel file with 2 worksheets

[RPYee]

I have a reporting process that was once done, very manually, by pulling raw data, copying it to Excel (full detail table), then creating a pivot table on a new worksheet (summarized matrix). The file is saved containing the two worksheets and is emailed to my audience as one attached Excel file.  I'm trying to automate this process.

I've created a paginated report in Report Builder that has both of these elements showing on the page, one table and one matrix.  When I run the report, it gives me 1228 pages for the complete detail table, then a bit of open space and the summary matrix table shows below, for a total of 1974 pages.  I want to export the table to a "Detail" worksheet and the matrix to a "Pivot" worksheet in one Excel file.  What is the best way to accomplish this end result?  

[Anonymous]

Hi, @RPYee 
Thanks for reaching out to the Microsoft fabric community forum.
 

You can first try the following blog, which introduces how to export Power BI reports to PDF/PPT/PBIX using PowerShell and send them as email attachments. You can modify the export format to xlsx based on this blog. The reason it is not mentioned is that the export API only supports paginated reports:

For details, please refer to:

Reports - Export To File - REST API (Power BI Power BI REST APIs) | Microsoft Learn

PowerShell Scripts to Export Power BI Reports to P... - Microsoft Fabric Community

2.Secondly, you can also try using simple R/Python scripts to schedule automatic data exports from Power BI:

For details, please refer to:

Schedule Automated Data Exports from Power BI usin... - Microsoft Fabric Community

3.Finally, we recommend using Power Automate to automate the process of exporting Power BI reports to Excel and sending them via email:

 For details, please refer to:

Export and email a report with Power Automate - Power BI | Microsoft Learn

How to Export Data from Power BI to Excel Using Power Automate

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[RPYee]

Thanks for your response. I should have included that we are currently using Power Automate and have been successful in creating and sending Excel files via email.  I just haven't figured out how to put two result sets into one Excel file.  My customers have been getting all the data in one file for years.  I hate to have to send two attachments because I'm changing over to Power BI.