Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate

This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.

I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.

With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link

It is possible !!!

And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here

How to set it up

In my workspace I have a simple report with 2 parameters – Year and Country

In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.

You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.

Next is to design the flow in Power Automate

In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.

Next step is to get the rows from the Excel file – So I use the List rows present in a table

This action will return the three rows and next is to use the Control “Apply to each” to loop through each row

For the output we choose the value from the “List Row…” step

Then we add the action “Export To File for Paginated Reports

This action will run a selected report from a specific workspace

We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.

Add the end of the Action window – we can specify the values for each of the parameters.

OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown

As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”

We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.

Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content

The flow is now ready for testing – Save you flow and hit the test button.

Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.

Hopefully you will see that all steps are successfully.

And we can check our mail box to see the report delivered

The recipients of the e-mail can be sent to any e-mail address.

Can we pay by the hour ?

In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.

You only pay for the time the capacity is on – and the list price per hour is $8.

Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.

Converting xls files to xlsx file using #powerautomate and avoid the pitfalls in #powerquery using xls

As described in this post – https://www.ehansalytics.com/blog/2020/2/15/avoid-using-excel-xls-files-as-data-source
there are issues to be aware off when you use xls files instead of xlsx in Power Query. See also this thread
https://social.technet.microsoft.com/Forums/en-US/41f2c8ec-1f2c-4591-ac6a-54764b2a90a7/bug-in-excelworkbookwebcontents-powerquery?forum=powerquery
.

Answering the twitter started by Imke Feldmann (https://twitter.com/TheBIccountant) thread by Ruth Pozuelo (go follow her excellent youtube – channel – link) encouraged me to write this post – – as I claimed we can convert the xls files to xlsx using Power Automate.

So here is a guide on how to do it

Convert xls files to xlsx

In the scenario I will use a trigger when an e-mail is received and use a rest API provided by https://cloudconvert.com/.

OBS – This is a paid service where you pay by the minute the conversion takes – price from $0.02 to $0.01 per minute.

First we start by selecting to build an automated flow and select the trigger “When a new email arrives (V3)”

Set the advanced options to only trigger when Attachments is included and include the attachments in the following steps in our flow.

As the next step I use an Azure Blob storage to store the file in the e-mail. When selecting the output from the previous step – power automate will automatically create an Apply to each container in which we can refer to each attachment in the mail.

In the Create Blob Action I connect to a blob storage and load the attachment into a preload folder

Now add a step where we create an HTTP request within the Apply to each container

In order to use the cloud convert REST API we need first to create a Process and use the process ID to create the conversion – documentation here

In the body property you specify your own APIKey and tell the process you want to do is a conversion from xls format to xlsx.

Next – add another HTTP request

We use a POST request again

And in the URI we use the result from the previous step as it returns a unique address to the process id from cloud convert

In the Body property we specify where

{

"input": {

"azureblob": {

"storageaccount": "<NAMEOFBLOBACCOUNT>",

"storageaccesskey": "<AccountKey>",

"container": "preload"

}

},

"file": @{body('Create_blob')?['Name']},

"timeout": 10,

"output": {

"azureblob": {

"storageaccount": "<NAMEOFBLOBACCOUNT>",

"storageaccesskey": "<AccountKey>",

"container": "converted"

}

},

"outputformat": "xlsx"

}

Here is the final overview of the steps needed.

Now send an email to the inbox you have connected your trigger to run.

And in our preload folder we can see the files

And in the converted folder we have the converted xlsx files

Hope this can help you converting your xls files to xlsx.

This will also make it much easier if you want to be able to setup

Power On!