Imagine this scenario:

You’ve got a Power BI Report built out and published to the service showing Total Sales over time for a collection of vendors. Since the vendors can’t enter your Power BI tenant – you decide you’d like to email each vendor a PDF version of the report page – with just their respective data.

You could do this manually using the Export to PDF functionality in the service, but with a list of 100+ vendors – this task would likely take a large chunk of the day – especially if you want to do this say once a week.

Alternatively – you could setup a Report Subscription using the Subscribe button in the Service – but this setting doesn’t give you the ability to filter the report page to be specific for each vendor (remember they don’t have access to the tenant).

You could convert the report to a Paginated Report, try to replicate the visuals, and then generate PDFs to send to vendors. More on this in another post, but let’s say you don’t have the time to jump into this Paginated Report rabbit hole.

As the title suggests – there is solution to this using Power Automate and Excel, but it comes with 1 clear prerequisite:

The report MUST be backed by Premium capacity (not Premium per User capacity).

This was the case as of my posting. I tested this solution with both types – and it only worked in Premium. Sorry if you were hoping for this to work with PPU 🤷‍♂️. Consider using an A4 Power BI embedded SKU to have Premium capacity in a pay-per-hour fashion – which is what I am doing. Just remember to turn it off when you finish!).

So how does this work?

We are going to setup this simple flow in Power Automate

This is obviously bare-bones and could be customized in 100+ different ways depending on your requirements.

Let’s look at how this all fits together.

The first part is simply a manual trigger – which means the flow only runs when we manually “push the button”.

Next we need to bring in some data from an excel file saved to OneDrive for Business. This file is where we will store all of the Vendor Emails and Report filter information.

For this connector to work – the Excel file MUST contain a Table (so make sure to convert your data to a Table.

The number of columns in this table can certainly grow depending on how complex your requirements are for sending each email. Having it all in one place makes management easy. More on the columns shortly.

The next step involves an “Apply to each” loop that uses the contents of the previous Excel step along with the Power BI Export tool (still in preview) and an Outlook step to send the emails.

Since we are going to be iterating through a list – all of these steps need to be inside of the loop as shown. I’m using the default settings on both the Apply to each and Power BI step. Some blogs may talk about changing the asynchronous pattern or concurrency, but I found that it worked for me if I left everything in the default setting shown below.

Since all of our inputs are managed in the Excel table – we just have to bring them in to the appropriate section in the Power BI Export tool. My sample report was only one page – but there are additional fields to add this if its necessary for your report.

Let’s specifically look at the ReportLevelFilters Filter section. In order to activate report-level filters, the input must be in the same format that also triggers a URL report level filter. You can find that format here.

The simple syntax is: TableName/ColumnName eq ‘value’

This is easy to test in Power BI Service. After the base URL add: ?filter=
and then the appropriate: TableName/ColumnName eq ‘value’.

Here’s what it looks like on our sample report:
The text in the red box is what is required for the “ReportLevelFilters Filter” part of the Power BI export tool.

The last step of our loop is to send an email:

Remember to include the file format as part of the attachment name.

At this point it’s a good idea to test the flow to make sure everything is working as expected. In my case – I have to ‘turn-on’ premium capacity for this workspace.

Also make sure to use an appropriate “test” email address for this step!

Here’s what the email looks like once received: 2 reports delivered to my inbox, each with an attachment name specific to the selected vendor.

When we open the second email attachment – we find a 1 page PDF with the report filtered to the identified vendor.

Pretty cool!

Each PDF report takes about 1 min to render – so this flow (with 2 reports) took just over 2 minutes to run.

But what if I’m doing this for 100 vendors as the initial task involved? Here’s where changing the Concurrency Control settings of the Apply to Each step can help, as it allows multiple runs to happen in parallel.

By adding parallelism to my test run – I was able to reduce the runtime from 2min to 1min.

For good measure – I also tried to run this same flow after switching my report back to Premium Per User capacity – and it returned an error – saying “FeatureNotAvailableError”.

Maybe this will change in the future…