Have you ever run a Power BI Paginated Report and found that your report is not as current as it should be? These issues often arise when paginated reports are built upon semantic models which require scheduled refresh events, because your data source does not support DirectQuery. Without Power BI premium licensing, you might be limited to eight refreshes per day, leading to inconsistent timing and data that’s frequently out-of–date.
Leveraging Power Query in Power BI Report Builder for Real-Time Data
With a recent update to Power BI Report Builder, you can now leverage Power Query directly within the tool. This allows you to connect to, import, and transform data from a wide range of data sources—including SharePoint Online lists and Excel files—that previously required a Power BI semantic model for data ingestion and transformation. Using Power Query, you can bypass the limitations of scheduled refreshes and generate real-time reports immediately after updates are made in your data sources.
Client Use Case: Report Automation for SharePoint Lists
We recently worked with a client to enhance a Power BI report designed for web use, and as part of this project, we determined that their business processes would benefit from the ability to produce traditional paginated reports. These printable reports are much more easily shared with users who might not have access to the interactive dashboard and can be easily archived for future review if needed.
Initial Solution: Building on the Existing Semantic Model
Initially, we built a Power BI paginated report on top of the semantic model which could be generated on demand. A week later, the team decided that they wanted to have a copy of each active report generated monthly. Suddenly, generating each report individually became a big chore. We quickly pivoted our solution and set up a Power Automate flow triggered “when an item is modified” in SharePoint. Since our business process required a review of the data for quality control, this seemed like a great solution because each report would be generated automatically during the review process, and the PDFs would be tagged and stored perfectly for easy access.
For detailed guidance on automating paginated reports with Power Automate, refer to Microsoft’s documentation here.
However, there was a challenge: The semantic model was only refreshed hourly, which meant our freshly generated reports were already outdated.
New Solution: Power Query in Power BI Report Builder
Luckily, Power BI Report Builder can now leverage Power Query which opens the possibilities to create paginated reports directly against hundreds of data sources without the need for Power BI artifacts like semantic models or dataflows. If we build our Paginated Report directly against SharePoint, then it will ALWAYS get the most current data possible!
Accessing the New Power Query Feature
To access this new feature:
- Right click on “Data Sources” in the left pane
- Select “Get data” from the menu
This action opens a familiar Power Query Online interface, similar to what you would see in Excel or Power BI Desktop. With this feature, you can directly query a SharePoint Online list and perform complex transformations by using Power Query M Language.
By querying the sources upon each report generation, rather than relying on scheduled model refreshes, you can ensure that your report reflects the most recent data.
Understanding the Limitations of Power Query in Report Builder
There are some limitations to be aware of:
No DAX Measures
You cannot create DAX measures within Power BI Report Builder using Power Query. All calculations must be performed using M Language in Power Query.
No Relationships Between Tables
In Power BI Desktop, you can create relationships (joins) between tables. Unfortunately, datasets cannot be “related” in the same way currently in Report Builder.
All necessary joins must be performed within your queries using merge operations.
No Helper Queries
You cannot use helper, intermediate, or function queries. All transformations must occur within a single query, which can lead to complicated M Language queries.
Building a Custom Function to Replace a DAX Measure
To overcome these limitations, we leveraged the Power Query M Language to perform all necessary data transformations and calculations we had built in the semantic model. For example, since DAX measures aren’t supported, we created custom functions within Power Query to replicate the logic of our DAX measures from the semantic model. This involved writing M code to perform calculations that would typically be handled by DAX, ensuring that the critical transformations required for our reports were included without resorting to complex SSRS expressions.
By handling these transformations within the Power BI workspace, we maintained the integrity and accuracy of our reports while benefiting from real-time data access.
If you’re facing reporting challenges or seeking deeper insights into your data, we’re here to help! Whether you need assistance with real-time data reporting, optimizing your Power BI reports, or automating your reporting processes, our team of experts is ready to collaborate with you. Contact us today to discover how we can enhance your data reporting and analytics capabilities.