Data Import & Export

How to Create a Power BI Report from Two Excel Pivot Tables

Maira Mehtab Sep 28, 2026 868 views

Question details

The user wants to recreate an analysis in Power BI that was originally built by combining two Excel pivot tables utilizing different month filters.

How to Create a Power BI Report from Two Excel Pivot Tables

Product: Microsoft Excel and Power BI
Device & OS: not provided
Scenario: Transitioning a combined pivot table chart with distinct time filters from Excel into Power BI using dataflows.
Observed behavior: The goal is to successfully load the source data from dataflows, merge it, and apply dynamic month filtering in Power BI to replicate the Excel charting experience.

Before you start

Ensure you have access to the Power BI Desktop application and the relevant dataflows containing your Excel source data. Note down the specific month filter logic originally used in your Excel file so you can accurately replicate it.

Solution 1Recommended

Recreate the Analysis Using Power Query and DAX

Load your dataflows into Power BI, clean the data with Power Query, and use DAX measures to handle the distinct month filters interactively.

Instead of importing the Pivot Tables directly, the best practice in Power BI is to import the raw source data. By using Power Query to prepare the data and DAX to handle the conditional month filters, you ensure a scalable and dynamic data model.

Load Data via Dataflows

Open Power BI Desktop, click on 'Get Data' in the Home ribbon, select 'Dataflows', and load your source tables.

Transform and Merge in Power Query

Open the Power Query Editor to clean and filter the data. If necessary, merge the two tables using their common key columns and click 'Close & Apply'.

Create a Date Table

Build a dedicated Date table in your Power BI model. Establish one-to-many relationships between this Date table and your dataflow tables.

Write DAX Measures

Create DAX measures for your specific calculations. Use the CALCULATE function combined with DATESBETWEEN or specific month filters to replicate the logic from your Excel pivot tables.

Build Visuals and Add Slicers

Add a chart visual to your report canvas and insert your newly created DAX measures. Add a Date slicer to the page to allow for interactive month filtering.

Recreate the Analysis Using Power Query and DAX

Scheduled Refresh: Once your report is published to the Power BI Service, remember to configure a scheduled refresh for your dataflows if the underlying data changes regularly.

Free Microsoft Office alternative

Handle Complex Spreadsheets and Pivot Tables with WPS Office

While advanced enterprise data modeling with Power BI requires the Microsoft ecosystem, WPS Office provides a lightweight, highly compatible, and free alternative for your day-to-day Excel tasks. You can easily create, manage, and combine complex pivot tables directly within WPS Spreadsheet without heavy subscriptions.

1. Open Your Data: Launch WPS Spreadsheet and open your existing .xlsx workbook.
2. Insert Pivot Table: Select your raw data range, navigate to the 'Insert' tab on the top ribbon, and click 'PivotTable'.
3. Analyze Data Dynamically: Drag and drop fields into the Rows, Columns, and Values areas to instantly replicate and analyze your data.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsAdvanced Pivot Table features for quickly summarizing massive datasetsLightweight installation with a familiar, easy-to-use tabbed interfaceFree to use for daily spreadsheet analysis and reporting

microsoft office alternative - wps office

Frequently Asked Questions

Can I connect Power BI directly to an Excel file containing Pivot Tables?

Yes, you can connect Power BI to an Excel workbook using the 'Get Data' > 'Excel' connector. However, it is highly recommended to import the raw data tables rather than the Pivot Tables themselves to build a proper, functioning data model.

Why should I use DAX instead of standard Excel filters?

DAX (Data Analysis Expressions) in Power BI allows for highly dynamic, context-aware calculations across massive datasets. It makes it easier to filter multiple tables simultaneously using a single slicer, which is much harder to achieve with static Excel pivot filters.

Do I absolutely need a separate Date table in Power BI?

Yes, creating a dedicated Date table is a fundamental best practice in Power BI. It ensures accurate time intelligence calculations and provides a single, unified way to filter multiple fact tables by month, quarter, or year.

Excel & Data Problems