Hello,
Our organization uses the suite of PowerBI and related Microsoft tools for reporting purposes. One of our use cases is that some of our users want to see Excel exports / reports in their inbox every morning (don't want to have to go to a dashboard for certain things).
I'm writing this post to ask for help or suggestions on the best (or at least alternative) ways of achieving this.
What we're trying to do is:
1. Pull data into a table in Excel (could be from a PowerBI dataset, OR from a more traditional SQL connector)
2. Develop some other reporting in Excel on top of that table - might include clean-up, creation of pivot tables, pivot charts, etc. but importantly, we want to ensure the user has access to a simple table with the raw data as well as anything else
3. Create an automated process that each day, refreshes the data and sends the report (as an attachment) to a number of different users. We'd like the processes / flows to sit in the cloud (don't want to rely on Power Automate desktop)
4. As a "plus" - I'm hoping to find a solution that is as simple and straightforward as possible (e.g., looking for a direct approach rather than an approach that relies on workarounds)
There appear to be a number of possible ways to do this, although we've found each has its drawbacks.
Approaches we have explored:
1. PowerBI Report Builder - works, but frankly our users find it somewhat clunky and difficult to build reports in Report Builder and to do simple things that are easy to do in Excel. I understand PowerBI Report Builder is a good solution for certain use cases, but doesn't seem like the right fit for ours.
2. Exportable Formatted Data Tables - newer functionality (https://powerbi.microsoft.com/en-us/blog/exportable-formatted-data-tables-preview/); this could work for some of our cases, although the functionality is a bit more limited to date (for example, I can't convert a date time field to a date without dipping back into PowerBI report builder).
3. Analyzing a PowerBI dataset in Excel - by this I mean either using "Analyze in Excel" from app.powerbi.com, or by choosing PowerBI datasets as a connector from within Excel. This could potentially work, but one of the limitations is that it defaults to allowing you to create only a Pivot Table in Excel using the data -- you can't export the data directly from PowerBI to Excel as a table without some workarounds (https://community.powerbi.com/t5/Service/Connect-to-dataset-from-Excel-as-a-TABLE-not-PIVOT/m-p/1209...). We also haven't tried but assume that a Power Automate workflow could refresh the Excel datasource on some schedule and then handle the sharing of the file to end users.
4. Power Automate + Importing data from SQL to Excel as a table - by this I mean we create a SQL connector from within Excel to our data. This works well, but we get stuck when we need to refresh the reports automatically. We save our Excel files to Sharepoint / OneDrive; Power Automate doesn't appear to be able to refresh online Excel reports because SQL isn't a connector supported by Online (only desktop/offline). I'd love to be wrong about this, but this is my understanding after some exploration.
5. Traditional PowerBI reports - this approach doesn't work for our particular use case because of the limitation that when a report is shared, it's a PDF or PPT, and our users are looking for the ability to access and review raw data in Excel. I know users can log into a report and export data from a visual to Excel, but unfortunately that workflow doesn't fit our needs (looking for Excel delivered automatically daily by email instead).
Options 3 and 4 get us closest to what we're trying to do. I'm sure there are other ways to go about this, and thank you to anyone in advance for helping point us in the right direction.
Thank you.
