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!

     

     

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

Thanks BA_Pete for your answer!

Just a quick clarification regarding second point:
2) Go to the Optmize tab > Performance Analyzer (PA) > Start Recording > Refresh Visuals.

I guess I'll also need to record the exportation from the visual table to excel, right? as this is the main goal.
So, the process will be:
2) Go to the Optmize tab > Performance Analyzer (PA) > Start Recording > Refresh Visuals > Export data > Save data in the Sharepoint folder

Thanks. 

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

     

    No. All this step (step 2) is doing is getting you the exact DAX query that PBI uses against the model to create your visual. You then use that DAX query (from step 3) in the Power Automate 'Run DAX Query' step so that Power Automate is querying your model in the Service in the exact same way that the report does.

    You then take the results of that query and create a Sharepoint file with it, also in Power Automate.

     

    Pete