Power Query Problems

Automatically Populate Weekly Route Averages in Excel Using Power Query

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to dynamically populate a smaller summary table of weekly route averages based on a selected week-commencing date, without creating and copying numerous IF formulas.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating and summarizing large datasets of daily and weekly route averages for specific selected weeks.
Observed behavior: The current process requires manual work or complex formula maintenance, prompting the need for an automated data extraction and filtering solution.

Before you start

Ensure your Excel version supports Power Query and Power Pivot (available in Excel 2016 and later, or Microsoft 365). Prepare your source data by formatting it as an official Excel Table for seamless import.

Solution 1Recommended

Transform and Filter Data Using Power Query and Power Pivot

Power Query offers a scalable and formula-free way to reshape large route data blocks into a structured dataset filtered by the week-commencing date.

Instead of writing complex nested IF statements, Power Query can flatten your dataset into a format that is easy to summarize. Once structured, you can filter by any selected date dynamically.

Load data into Power Query

Select any cell inside your source table containing the daily and weekly averages, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to launch the Power Query Editor.

Unpivot the date columns

Select your fixed route identifier columns, right-click the column header, and choose 'Unpivot Other Columns'. This action transforms your wide, date-based route blocks into a flat, structured format with 'Attribute' (Date) and 'Value' columns.

Filter by the week-commencing date

Click the dropdown arrow on your newly created Date column. Select 'Date Filters' and apply a filter to match your selected week-commencing date parameter, ensuring only the relevant week's data remains.

Load to the Data Model

Click the 'Close & Load' dropdown and select 'Close & Load To...'. Choose 'PivotTable Report' and check the box for 'Add this data to the Data Model' to utilize Power Pivot for advanced average calculations.

Highly Scalable Workflow: This setup automatically scales. When you add new weekly route blocks to your source table, simply click 'Refresh All' on the Data tab to update your final averages table.

Easily Calculate Weekly Averages in WPS Spreadsheets

WPS Spreadsheets provides robust PivotTable features that allow you to quickly summarize daily route data into weekly averages without writing a single complex IF formula or using advanced database add-ins.

1. Select your dataset: Highlight the entire route data table in your WPS spreadsheet that contains the dates, routes, and values.
2. Insert a PivotTable: Navigate to the 'Insert' tab and click on 'PivotTable' to create a new summary table on a fresh worksheet.
3. Configure Rows and Values: Drag your 'Route' field into the Rows area, and the 'Values' field into the Values area. Left-click the Values field, select 'Value Field Settings', and change the calculation from Sum to Average.
4. Add a Date Filter: Drag your 'Week-Commencing' or 'Date' field to the Filters area. You can now use this top dropdown filter to select a specific week and dynamically update your weekly averages table.

Create dynamic PivotTables to summarize route data instantlyEasily filter route averages by specific week-commencing datesFully compatible with Microsoft Excel (.xlsx) file formatsLightweight architecture runs smoothly on older devices

microsoft office alternative - wps office

Frequently Asked Questions

Can I automate the data refresh when new weekly route averages are added?

Yes, if you use a PivotTable or Power Query to generate your weekly table, you can simply right-click inside the resulting summary table and select 'Refresh' to instantly update the data with any newly appended rows.

How do I avoid writing complex nested IF statements for date filtering?

By restructuring your data into a flat, tabular format (e.g., Date, Route, Value) and using Built-in PivotTables or Power Query, you eliminate the need for IF formulas entirely. These built-in tools handle conditional filtering and aggregations natively.

Will this Power Query solution work on older Excel versions?

Power Query is built into Excel 2016 and Microsoft 365. If you are using Excel 2010 or 2013, you will need to download and install the free Power Query add-in from Microsoft's website. Alternatively, standard PivotTables with an AVERAGE calculation are universally supported.

Excel & Data Problems