Power BI continues to evolve, and while many capabilities have moved to the web, certain features like mail merge still require the robust functionality of the Power BI Paginated Report Builder. If you’ve ever needed to send personalized invoices or letters via email using Power BI, this guide will walk you through the steps to achieve a mail merge using Paginated Reports.
Why Mail Merge Still Needs Report Builder
Recently, Power BI introduced the ability to design paginated reports directly in the web portal, which led to questions about whether it’s now possible to perform mail merges online. Unfortunately, the answer is no, at least not yet. Advanced features like mail merge require more flexibility and control than the current web-based designer offers. That’s why we still need the desktop version of Power BI Paginated Report Builder for this process.
Step 1: Prepare Your Data Sources
The first step is to set up your data. In this example, I uses SQL Server and creates two views:
- Customer Listing View: A simplified list of customers with basic details like name and email. This is used to parameterize the report.
- Customer Invoice View: A detailed dataset containing customer names, email addresses, and invoice details like product names, sales amounts, taxes, and freight.
By structuring your data this way, you allow for selective filtering and more personalized output per customer.
Step 2: Connect to Data in Report Builder
Open Power BI Paginated Report Builder, then connect to your SQL Server views via the “Get Data” option. You’ll create two datasets—one for customer listings and one for invoice data.
Next, configure your datasets to include parameters. You can follow the “IN Parameter” pattern shown in previous tutorials to filter invoices based on the selected customers.
Step 3: Avoid Blank Pages When Printing
Before designing your report, check out the official documentation on avoiding blank pages in paginated reports. This is essential to ensure your mail merge prints cleanly and efficiently.
Step 4: Design Your Mail Merge Template
- Use the List Control: This control provides a flexible canvas similar to a free-form layout.
- Add Placeholders: Insert text boxes for the customer name, address, city, state, and postal code. You can use expressions to concatenate these fields if needed.
- Group by Customer Key: Set up a group using the CustomerKey field to create a page break for each unique customer.
- Insert a Table for Invoice Details: Add columns for Product Name, Sales Amount, Tax, Freight, and a calculated SubTotal column (SalesAmount + Tax + Freight).
Once your layout is complete, add headers, salutations, and styling to polish the look and feel of your document.
Step 5: Preview and Publish the Report
Run your report in Report Builder to ensure each customer receives their own page with personalized invoice data. Once finalized, publish your RDL file to the Power BI Service.
Step 6: Automate the Distribution
In the Power BI Service:
- Create Subscriptions: You can create static and dynamic subscriptions.
- Static Subscription: Save reports to SharePoint or OneDrive.
- Dynamic Subscription: Automatically send customized reports to email addresses from your dataset.
When using dynamic email delivery, ensure your recipient list is accurate to avoid spamming unintended contacts.
Final Output
Each customer receives a neatly formatted PDF with their personal invoice details, delivered either to their inbox or a centralized location like OneDrive.
Conclusion
While mail merge isn’t yet possible in the Power BI web designer, using Paginated Report Builder provides the full flexibility needed to create dynamic, personalized documents. This solution is perfect for generating invoices, statements, or any customer-specific reports at scale.
Looking to dive deeper into Power BI? Explore hands-on, instructor-led Power BI training sessions with Data Bear.
