Power Query Problems

Design an Excel Summary Workbook for Large Monthly Datasets with Power Query

Steven ParkerSteven Parker Nov 12, 2026 868 views

Question details

Create an efficient workbook structure for large monthly datasets, consolidate the data into a summary, and use the summary for a bar chart.

Design an Excel Summary Workbook for Large Monthly Datasets with Power Query
Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
The user needs to combine recurring monthly datasets while keeping the reporting workbook responsive and easy to update.
Observed behavior
Large source files and formula-heavy workbooks can become slow when they contain hundreds of thousands of rows.
Before you start

Store each monthly source file in one folder and make sure every file uses the same column headers, data types, and category names. Remove merged cells, subtotal rows, and blank header rows before connecting the folder to Power Query.

Solution 1Recommended

Consolidate monthly source files with Power Query and load a summarized table

Use this method when each month is saved as a separate file with the same layout and you need a refreshable summary without extensive worksheet formulas.

Power Query can append the monthly files into one query, then group the records by category and month. Load only the final summary to the reporting sheet to reduce workbook calculation load.

1
Create the reporting workbook structure

Open a new Excel workbook and add a worksheet named Summary. In Column A, reserve space for chart categories or groups, and use Columns B onward for monthly values if you want a month-by-month matrix.

2
Connect to the monthly file folder

Select Data > Get Data > From File > From Folder, choose the folder containing the monthly files, and select Combine & Transform Data.

3
Standardize the combined query

In the Power Query Editor, confirm that the first row is used as headers, set the correct data type for each column, and remove columns that are not needed for the report. Make sure category and date fields are consistently formatted.

4
Create the monthly summary

Select Home > Group By in Power Query. Group by the category field and month field, then choose Sum for the value or amount column. If needed, select Transform > Pivot Column to place each month in its own column.

5
Load the summary and create the chart

Select Home > Close & Load To, choose Table, and load the result to the Summary worksheet. Click inside the loaded table, then select Insert > Bar Chart and choose the preferred bar chart type.

Consolidate monthly source files with Power Query and load a summarized table
Refreshable monthly reporting: When a new monthly file is added to the same folder with matching headers, select Data > Refresh All to update the query, summary table, and connected chart.
WPS Spreadsheet reporting tools

Build a lightweight monthly summary and bar chart in WPS Spreadsheet

WPS Spreadsheet helps you keep large monthly reports manageable by separating raw data from a compact summary table. Use its data consolidation tools to combine values by category, then create a bar chart directly from the summarized results.

  1. 1. Create a Summary worksheet: In WPS Spreadsheet, select the plus button beside the sheet tabs and rename the new worksheet Summary. Enter categories in Column A and month headings across Row 1.
  2. 2. Prepare matching monthly ranges: Place each monthly dataset in a consistent tabular layout with the same category and value columns, or open each monthly workbook and identify the matching data range.
  3. 3. Consolidate the monthly values: On the Summary worksheet, select the destination cell, then choose Data > Consolidate. Select a consolidation function such as Sum, add each monthly source range, and confirm the consolidation.
  4. 4. Convert the output into a chart source: Select the completed summary range, including the category labels and month headings, then choose Insert > Chart.
  5. 5. Insert and format a bar chart: Choose a Bar chart type, then use the chart options to add a title, adjust the legend, and confirm that categories appear on the correct axis.
Create a category-by-month summary table for large recurring datasets.Use Data > Consolidate to combine matching category values from multiple ranges.Create bar charts from a compact report table instead of detailed raw records.Open and save Microsoft Excel .xlsx and .xls workbooks with format compatibility.
microsoft office alternative - wps office

Frequently Asked Questions

Should I store every monthly dataset on a separate Excel worksheet?

For very large datasets, keeping monthly files in one folder is usually more efficient than placing every raw dataset in one workbook. Use Data > Get Data > From File > From Folder to combine the files through Power Query.

What must match before I combine monthly files in Power Query?

Each file should use the same headers, column order where possible, data types, and category naming. Power Query can append files more reliably when their source tables have the same structure.

How do I add a new month to an existing Power Query report?

Save the new file in the same source folder, use the established headers and format, then open the summary workbook and select Data > Refresh All.

Why is my large Excel workbook slow after adding formulas?

Formulas such as VLOOKUP, INDEX/MATCH, SUMIFS, and volatile functions can recalculate across large ranges. Use Power Query to transform and aggregate the raw data, then keep formulas limited to the small summary table.

How can I make the bar chart update after monthly data changes?

Create the chart from the table loaded by Power Query. After selecting Data > Refresh All, the loaded table updates; a chart linked to that table updates with the refreshed values.