Forum Discussion

J_S_A's avatar
J_S_A
Icon for Helper I rankHelper I
1 year ago
Solved

Automation of Excel generation from Power BI

Hi, I have a visual table created in a Power BI Report in Power BI Desktop. I would need to export this table to Excel daily and save it in Sharepoint. Is there any way to achieve this in an automa...
Need Help
  • Olufemi7's avatar
    Olufemi7
    1 year ago

    Automating Excel Generation from Power BI (Without Premium)

    Hi J_S_A , 

    I had a similar need  exporting a visual table from a Power BI report daily to Excel and saving it to SharePoint and I found a practical solution using Excel, Outlook, SharePoint, and Power Automate, all without needing Power BI Premium or PPU.

     

     Here’s how I achieved it (No Code Required):

    1. Export the Visual Table to Excel

    • First, I manually exported the visual table from Power BI Service to Excel.
    • I saved the file in a SharePoint document library.

    2. Automate the Daily Email with Power Automate

    • I created a Scheduled Flow in Power Automate:
      • Trigger: Recurrence (daily at a set time).
      • Action 1: Get file content using path from SharePoint.
        • Site Address: Your SharePoint site.
        • File Path: /Shared Documents/YourFile.xlsx
      • Action 2: Send an email (V2) via Outlook.
        • Attach the Excel file.
        • Customize subject and body.

    This setup sends the Excel file automatically every day via Outlook and keeps it stored in SharePoint.

     

    Benefits

    • No need for Premium or PPU.
    • No coding required.
    • Fully automated using Microsoft 365 tools.

    Let me know if you'd like a step-by-step guide, happy to share what worked for me!

     

     

BA_Pete's avatar
BA_Pete
Icon for Super User rankSuper User
1 year ago

Hi J_S_A ,

 

Yes, you can do this using Power Automate. Here's a basic outline of what you'll need to do. I won't list the whole lot here as parts of it can be tricky and require local adjustments, and this is a Power Query forum, after all:

 

1) In PBI Desktop, select the page with the visual you want to export on it.

2) Go to the Optmize tab > Performance Analyzer (PA) > Start Recording > Refresh Visuals.

3) Once your visual has refreshed and create a PA entry in the list, expand the entry and choose Copy DAX Query

4) In Power Automate, use the Run DAX Query action with your copied DAX query against your model in the PBI Service.

5) Complete Power Automate flow to Create File in Sharepoint.

 

As I said, this is a really rough outline but hopefully gets you started in the right direction.

If you need further details on the Power Automate side, I suggest you ask over on the Power Platform forums, here: Microsoft Power Platform Community Forum Thread

 

Pete