Power Automate for archive - but not PDF, PNG or PPT

[NovaBI]

Hi all,

is there a way to use PowerAutomate to archive Power BI reports from a Prem.Cap.?

The only formats that are available are pdf, ppt and png. It does not make sense if you have scrollable visuals and the users want to see data from the past e.g..

I know that there is a way with the RESTAPI, but I am looking for a solution to save the .pbix on e.g. a monthly basis, and this needs to be done by the specific workspace owners, not the tenant admin (as i dont want to use a singles person log in for all individual automatisation).

[Jeanxyz]

There is a new Power Automate feature released recently which allow users to export different tables in csv based on a schedule defined by user. 

I don't think it's possible to archive the whole pbix dataset, you will have to export the data table by table. Here is an introduction on the Power Automate feature.

https://www.youtube.com/watch?v=WsbIXJDhC_o

Please accept the answer as a solution so that other users can benefit from it as well. 

[NovaBI]

Hi @Jeanxyz ,

thanks for your answer.

I already saw that video by Curbal. 

There are some shortfalls in this method too. First the limitation to 100k rows.

And 2nd, users tend to compare same to same. I.e. if I offer to have a fully blown report form current month and a flat file, it is not the premium solution from my point of view.

I am aware that PBI is designed to represent data, but as you can export the pbix manually, I never understood why MS is not offering that method as an automated one.

[Jeanxyz]

I agree that there are limitations with curbal's approach. But archive the pbix file won't solve your problem because you can't really compare a pbix file downloaded on Jan 1 with the report downloaded on Feb 1st. The moment you open a pbix file, it's automatically connected to the source data and get refreshed (if your source data is a live dataset). Hence the pbix file is always reflecting database at the moment. 

In my case, I use Power Automate to archive fact tables on weekly basis, use Power Query to consolidate csv files into a snapshot table, and load the snapshot back into the pbix report. In this way, I can create visuals to show data evolution from month to month. 

[NovaBI]

Not sure, but if the pbix is stored and the auto. refrsh. is disabled, i dont see any issues.

It must be the same as if i download the pbix to my desktop and just not hist refresh. old data.

live connection is only reasonable when you have a performant lake or db with that pbix size. Not the case here.

Users/customers do not live in the perfect world 😉

[Jeanxyz]

Thanks for the reply. I did a few tests and you are right: when a pbix file is opened from desktop or pb service, the dataset in the report itself won't get refreshed. 

Below are the cases when data refresh will take case in Power BI desktop and PB service. So it looks indeed if a copy of the pbix download will provide a copy of dataset snapshot. However, I haven't find a Power Automate to do the job. Here is a link about how to do execute pbix download using PowerShell script (https://powerofbi.org/2021/02/12/powershell-script-to-download-power-bi-report-from-power-bi-service...)

In Power BI Desktop refresh of report visuals, data refresh, and schema refresh all happen together using

1. Home ribbon > Refresh button
2. Home ribbon > Transform data > Close & Apply button
3. The context menu (right-click or click on ellipsis) on any table then choosing Refresh data

In PB Serverice, data refresh takes place in the following cases

1. Scheduled refresh
2. Refresh now
3. Refresh a Power BI dataset from Power Automate
4. Processing the table from SQL Server Management Studio (Premium)

Detailed documentation on data refresh in Power BI:

Data refresh in Power BI - Power BI | Microsoft Docs

[Jeanxyz]

If your source data comes from SQL server, you can create snapshot in SSIS without Power Automate. 

[NovaBI]

We are about to decomission SSIS, as it is an reliable (more or less..) but old tool.

And the source is not a SQL one. Sorry 🙂