Frequently my clients love the visualisation and aggregation of Power BI but want to be able to print off a scrollable list. However, this is one key element that is not part of Power BI, you need to use paginated reports which is included within Premium licensing. Paginated Reports give the ability to create a printable report on several pages of a table.
However, I think with the help of a blog post from Matt Allington, which I used to learn how to do this, I have a work around using Power BI, Excel, DAX studio and VBA. Very excited about this 😊
If you have not signed up to Matt’s emails, most definitely do. It is an invaluable source of information.
Warning, this is not a, “you should do it this way for best usage”, this is more of a “this is how I did it” blog post.
First step create the report in Power BI Desktop and create the visual that you want to replicate as a printable report. I have created a table based on population data.
I publish the report to the Power BI service so that I can have access to the dataset in Excel.
In Power BI Desktop, I use Performance analyzer to run the query and capture the DAX code required to create the visual. I tend to run the query in DAX studio because I like to see how it is created.
Then I open Excel. You need to make sure that your version of Excel is licensed to the same username and tenant that has access to the Power BI dataset in the service. Using the Get Data option in the Data tab, choose Power BI Dataset which will give you a list on the right-hand side of the page. Choose the appropriate dataset.
Here is a link to how I did it. Basically, you create a connection to the dataset by creating a pivot table, then you can change the query to the specific query you want to run, captured in DAX studio.
This works perfectly, and to refresh you must right click in the table and press refresh.
The only issue is that the refresh can overwrite some of the formatting which is required for printing. Anything outside of the table can be customised as it will not change when you refresh the table so headers etc are perfect. You can set the rows to titles in the print settings of Excel so that they are replicated on each page.
My VBA is rudimentary and basic, but in the same way that I use performance analyser and DAX studio to capture the DAX code, I can create the VBA code by creating a Macro by pressing record to capture my keyboard movements. My tip would be, do not use a mouse to do anything, use the keyboard and plan the sequence of the steps.
Once you are happy with your code, then create a button using shapes, assign the macro to it and voila, you have a scrollable pdf from a table within Power BI.
