Power BI Export All Visuals - python notebook

[mike_honey]

I've created a python notebook that helps to automate review and testing of Power BI reports. It embeds a report and then works through every (visible) page and exports the data from every (visible and exportable) visual.

The data is exported in CSV and/or Excel format (both by default). A folder is created for the output, then a sub-folder is created for each page, and a file is created for each visual.

This project is mainly intended to help test Power BI reports. Any change to a report's input data, queries, semantic model, or page designs can affect the results shown to the end users. Bugs or unexpected results can quickly destroy the confidence of your audience and frustrate your testers/reviewers. These can be as minor as leaving a slicer or filter set to an inappropriate selection when the report is saved and published.

This project can quickly capture snapshots of all the data presented in open formats (CSV, Excel) that can be independently reviewed and compared using a range of tools and techniques.

When used to produce CSV files, the project can be used in combination with the Visual Studio Code Compare Folders Extension to very quickly compare every cell of data from every visual in the report. This could be useful for a "regression test" to determine whether changes between versions have only had the desired effect and have not leaked into other pages or visuals.

When used to produce Excel files, a range of techniques can be used to analyse the output. My favourite tool is the Inquire / Compare Files feature of Excel (Inquire Add-In). This works row by row and column by column to compare 2 Excel files, to quickly highlight all the differences for review.

I usually start with the VS Extension against CSV files for a first pass of review, then use the Excel Compare Files Add-In when more complex changes need to be reviewed.

I zip up the folders of output from each test/review cycle and stash them e.g. to a SharePoint document library. These are "proof" of my testing/review, that any analyst can open and review themselves independently.

The notebook embeds a live frame containing the target report. So if you need to first apply specific filters or use slicers, you can run the notebook section-by-section or cell-by-cell down to get the embedded report frame, then interact with it just like it was a Power BI web browser tab. When you run the remaining notebook cells, the output will reflect your filter or slicer changes. Any changes made will not be saved back to the Power BI web report definition.

I've made this notebook freely available in a GitHub project, so anyone can quickly get started to review and test their own reports. There are more technical notes there, including the requirements, but let me know if you get stuck on anything or raise an issue in GitHub.

https://github.com/Mike-Honey/powerbi-export-all-visuals?tab=readme-ov-file#readme