Power Query for Power BI Report Builder (Paginated Reports)

In this insightful tutorial, Manuel Quintana from Pragmatic Works explores a groundbreaking update for Power BI Report Builder (Paginated Reports) – the introduction of Power Query for data transformation within paginated reports. This new feature, currently in preview, enhances data preparation capabilities, making it easier for report designers to combine data from multiple sources directly within the Report Builder environment.

What Is Power BI Report Builder?

Power BI Report Builder, also known as Paginated Reports (formerly SSRS), is a tool used for creating pixel-perfect, print-ready reports. While its core functionality has remained relatively unchanged for years, the introduction of Power Query brings a transformative layer to the report-building experience.

Introducing Power Query for Paginated Reports

Power Query, a powerful data transformation tool previously limited to Power BI Desktop and Dataflows, has now been integrated into Power BI Report Builder. This feature offers:

• Data Transformation: Filter, sort, merge, and cleanse data directly within the paginated report environment.
• Expanded Data Connectivity: Access a wide range of data sources, including Azure SQL, Lakehouses, OneDrive, and more.
• Intuitive UI: A familiar, user-friendly interface for transforming data without needing complex coding.

Setting Up Power Query in Power BI Report Builder

To use Power Query within Paginated Reports:

1. Download and install the latest version of Power BI Report Builder.
2. Navigate to the Data tab and select the new Get Data (Preview) option.
3. Choose your data sources (e.g., CSV files from OneDrive, Lakehouse tables).
4. Transform your data using the Power Query editor.
5. Load the transformed data as a single dataset for use in your paginated report.

Key Benefits of Using Power Query in Paginated Reports

This update offers several advantages for report designers:

• Streamlined Data Preparation: No need for complex expressions like LOOKUP or LOOKUPSET.
• Data Consolidation: Merge multiple data sources into a single dataset, simplifying report creation.
• Consistent Experience: Leverage the same transformation tools available in Power BI Desktop and Dataflows.

Current Limitations (Preview Phase)

Since this feature is still in preview, there are a few limitations to be aware of:

• No Gateway Support: On-premises data sources are not yet supported.
• Privacy Levels: The current version lacks UI controls for defining data privacy levels, which may restrict combining data from multiple sources.
• Single Dataset Restriction: Only the bottom-most query in the Power Query editor is imported as a dataset.

Use Case Example: Combining CSV and Lakehouse Data

Manuel demonstrates how to combine data from a CSV file in OneDrive and a Lakehouse table within the same paginated report:

1. Imported both sources using the Get Data feature.
2. Performed a Left Outer Join to merge data tables.
3. Transformed data directly within Power Query.
4. Loaded the combined dataset for reporting, eliminating the need for complex expressions.

Conclusion

The introduction of Power Query into Power BI Report Builder is a game-changer for creating paginated reports. It simplifies data transformation, reduces reliance on complex expressions, and enhances data consolidation from multiple sources. While the feature is still in preview, it offers significant potential for the future of report development in Power BI. Stay tuned for more updates and tutorials from Pragmatic Works as this feature continues to evolve!

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.