Scheduling an email with an excel attachment from a pbi report. Is it possible?

[Pateen]

Hi There,

I want to send out a daily email from my PBI report and attach an excel copy of the report so users can quickly analyse their data when they recieve the attachment.

I thought it would be very easy in the subscribe to emails section but the only attacement options are PDF and Power point.

Is there somewhere else I need to go so I can add an excel report attachement?

Many Thanks,

Pat

PS I am a premium user

[Pateen]

Hey Stuart,

Thank you, your prompt responses are great.

In the data set I work with, that data gets overwritten daily. E.g a store item with changing stock values.

It doesn’t keep a historical snapshot of the data in the data set as there are a huge amount of items to consider and the way the database is designed.

As you said above, slicers etc work very well when data is appended to the data set but not for continually changing values.

I though attaching an excel report wasn’t but hugely complicated feature but I guess I'll need to rethink my solution to providing users with daily snapshots of data.

Any thoughts on how may review my approach?

Pat

[Burningsuit · Accepted solution]

Hi @Pateen 

If the original data you're working with gets overwritten each day, you'll going to need to have a "History Store" somewhere. This can be a Power BI Dataset, depending on the size and shape of your data. It's difficult to estimate dataset size, because it depends on how repetitious your data is, but I have Pro licence Datasets with over 10 million records in it, but as I said your mileage may vary depending on your data.

The other alternative is to create a Table in SQL and store the data there, but that assumes you have the access and ability to create SQL tables.

If the size is manageable I'd hold it in the Power BI Dataset, in Power Query I'd tag the data read with the date and keep growing the dataset. Of course you could also just hold a rolling n months of data. Power Query is your friend here.

But first I'd talk to the users. How often will they need this? and how far back do they want history to go? Is it just days, or months or years ? Importantly how do they cope with this at present ? And do they *really* use it, rather than just think it would be a "nice to have".

It so often occurs that what seems a simple feature starts to get complex once you really look at it. But understanding how Power BI (which is really best as a Reporting tool, not an Archival/Data Snapshotting tool) works will help you use the right tool for the job, and in my experience the right tool is seldom multiple individual Excel exports.

Hope this helps

Stuart

[justinh]

This is not solved. There are 100% legitimate business cases for needing an excel file emailed daily. Hopefully I can find a solution for this soon, becuase it's needed. If anyone knows how to automatically email out excel files with data updated from a published Power BI dataset/Semantic model, please let me know. 

[Pateen]

Just nudging this up there to ask if anyone knows a way of auto generating an daily excel output from PBI.

I'm trying to avoid users needing to log in and create their own daily excel downloadsas it will be very time consuming based on the number or reports.

Any tips guys?

[Burningsuit]

Hi @Pateen 

PDF and PowerPoint are the only options for Report subscription exports.

If you want to export Excel you'll need to generate a Paginated Report, that can export data as Excel, CSV and other formats.

See: Paginated reports in the Power BI service - Power BI | Microsoft Learn

The bigger question, is why do you need to export Excel files to them? That rather ruins the idea of "one version of the truth" held in your Dataset, if there's going to be all these Excel files swimming about. Users have a habit of "just updating my numbers" and suddenly you've got multiple Excel analysis sheets out there possibly conflicting with the central Dataset and each other.

Why don't you use "Analyse in Excel", "Personalized Visualisations" or give them build rights on the Dataset if you want your users to do their own analysis ?

Hope this helps

Stuart

[Pateen]

Thanks for your response Burningsuit,

To answer the bigger question you’ve asked. One reason for the excel attached to email solution is that so the users will have a daily snapshot of the data in their inboxes and may refer to it anytime in the future to help answe what they need to know.

The second reason is that, why should a user have to spent time every day loggin into PBI to generate a snapshot of data in case the need to look at it several days later.

I find it strange that Microsoft haven’t provided this type of feature considering both products are MS products when even old fashioned Business Objects has this type of feature.

Unless of course I’m completely missing something with PBI

[Burningsuit]

Hi @Pateen 

I think you are missing the way Power BI likes to operate, which should solve your problem.

1) The Power BI Service runs an auto-refresh every day (or more often if required) which loads your Data, through Power Query into the Power BI Dataset. 

2) No Data in the Dataset is removed, it keeps growing (Power BI can handle very large Datasets or in step 1 you can just load data from a rolling 6 month or so ago).

3) Your Dataset has the Date, and the Records loaded in multiple rows

4) You create a Report page for the users, with a date slicer. By using this data slicer they can "go back" to see any date in time in the Dataset and view their figures for this date.

 You can finesse this solution with Row Level Security so users only see their own data and other things to make the Report more user friendly etc, but the basic idea is that you don't need a "Daily Snapshot" because all the data for each day is held in the Dataset, users then use the Date slicer to move backwards and forwards in time through this data.

Your data is held centrally and securely, you have one version of the truth to refer to that everyone sees (if allowed to), you don't end up keeping history in multiple separate spreadsheets.

Your request, to have multiple downloads of excel spreadsheets for multiple users with no governance about what happens to that data or how it is represented, feels very like the sort of problem Power BI was designed to solve.

Hope this helps

Stuart