Paginated Reports have been available in Power BI since 2019. They serve an important purpose, but they are not easy for the average business user to learn, plus they require Power BI Premium to use. In my blog and video today, I will show you how you can use Excel as a substitute for Paginated Report Builder to build simple paginated reports from your Power BI Desktop data model.
What is a Paginated Report?
As the name suggests a paginated report is a report that spans across several pages. In Power BI Desktop, if the report spans more than a single page, you get a scroll bar that allows you to see all the data. This is fine, as long as you are interacting with the report live at the time. But you cannot print a Power BI report that has a scroll bar and still see all the data. Enter paginated reports to solve that problem.
In this blog and video I will follow the techniques that I described in my previous blog articles.
https://exceleratorbi.com.au/extract-tabular-data-power-bi-service-excel/
https://exceleratorbi.com.au/introduction-dax-query-language/
https://exceleratorbi.com.au/dax-query-language-part-2/
https://exceleratorbi.com.au/uses-dax-query-language/
Power BI Paginated Reports using Excel
-
Build a table visual in PBI. The table layout should be the same as you need in your paginated report. You can add column, measures, and implicit measures to build out your table.
-
Open the Performance Analyzer pane by selecting the option in the top menu. Click on Start Recording in the Performance Analyzer pane.
-
Refresh the table visual.
-
Copy the query generated by the table in the Performance Analyzer pane.
-
Launch Excel with a connection to the Power BI Desktop data model. You can use the third-party tool Analyze with Excel from SQLBI installed as an External tool in Power BI Desktop. It will also work with Analyze in Excel from PowerBI.com
- Build a simple pivot table in Excel from the connected data model.
- Extract a table linked to the data model by double clicking on any value in the pivot table.
-
Edit the DAX to render the table you need.
- Right-click anywhere in the extracted table.
- Click on Edit DAX.
-
Replace the query code with the query code that you copied from the Performance Analyzer pane in Power BI Desktop. You need to edit the copied query so that all the rows and not just top 501 rows get generated.
- You may need to edit the column names in the table so that they represent the content that is displayed.
-
Set the page printing options required for the paginated report.
Print preview the paginated report.
-
Export the paginated report to PDF.
Check that you got the paginated report as you wanted.
The Video
In this video I illustrated all the required steps to generate a paginated report from Power BI Desktop. I have also shown what would be happening under the hood to generate the results.
Renaming Column Names
In the video, I show that you need to rename the column names if you don’t like what you get. Unfortunately it is not currently possible to do this with DAX. The downside of renaming the column names like I showed in this video is that each time you refresh the data, the column names will revert to the default. An alternate is to add a new row above the table and use that as the header row, then hide the row of column names from the report.
Hello Matt!
Thank you for this very interesting article and video.
When I try to make use of this technique, I run into an error message when double clicking on the value in the pivot table in Excel. The message translates roughly to “The table KPIs cannot be used in calculations because it contains no columns”. So that stops me dead in my tracks.
Have you encountered this, and to you have workaround?
I suspect it’s somehow related to the fact that most measures in the Power BI datamodel have been moved into a dedicated “Mesasures Table” called “KPIs”, which is a technique recommended by several sources. This table only contains measures, sort of what the error message is referencing (no columns).
Your thoughts on this will be greatly appreciated.
Many thanks in advance!
I haven’t heard of this. Can you test it by creating a test measure in one of the fact table?
This is great explanation Matt.
Do you know if it is possible to apply a DAX measure used to conditional format the background colour in the Power BI table to the excel table? After I copy and edit the query, I can see it has come through as its own column in the excel table that lists the HEX colour code for each row based on the conditions in the DAX measure.
Thank you
Technically there is no link between DAX and Excel, so I can’t see how it can work. You could consider Inforiver and see if you can use the export to Excel features to inherit the background colour
https://exceleratorbi.com.au/deep-dive-into-inforiver-export-to-excel/
Great article Matt and thanks for the video to explain the process.
Yes you can publish, but not auto refresh. You will have to manually do that.
Definitely going to be making use of this idea. Do you know if it’s possible to publish the final Excel to the Power BI Service and have it automatically refresh whilst there..?