Create Weekly Average Rows with Power Query in WPS Office Spreadsheets
Question details
The user wants to create weekly average rows from multiple date-based columns in a source table and load one summary row per week into a separate result table using Power Query, with the output refreshable when source data changes.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A source table contains multiple date-based columns and measures, and the user needs weekly aggregated averages that update automatically in a separate result table.
- Observed behavior
- One result row per week is loaded into the output table, and the query can be refreshed to reflect new or changed source records.
Make sure the source data is in a proper Excel table, such as Table1, with consistent date columns and numeric measure columns. Open the workbook in WPS Office Spreadsheets and confirm that you can access the Power Query Editor from the Data tab.
Create the weekly average query by unpivoting and grouping in Power Query
Use this when you want to build the main transformation that turns multiple date-based columns into one weekly summary row.
Because the source table stores dates in multiple columns, unpivoting converts those date columns into rows so Power Query can group every measure by calendar week. After averaging, you can pivot the results back into columns to match the desired Table2 layout.
In WPS Office Spreadsheets, click a cell inside Table1, go to the Data tab, and select Get Data > From Table/Range to open the Power Query Editor.
In the Power Query Editor, select all date columns that hold weekly dates, right-click the selected headers, and choose Unpivot Columns so each date column becomes an Attribute and Value pair.
Select the unpivoted value column that contains the dates, go to Add Column > Date, and choose Week > Start of Week to create a column that identifies the calendar week for each row.
Use Home > Group By, set Group by to the new week column, add an aggregation for each measure column, set the operation to Average, and click OK.
If the result should display each measure in separate columns, select the attribute and value columns and use Transform > Pivot Column; then use Home > Close & Load To and select Table2 or a new worksheet as the destination.
Refresh the weekly average table after adding new source records
Use this when the source table changes and the result table needs to show the latest weekly averages.
Create weekly average rows with Power Query in WPS Office Spreadsheets
Use WPS Office Spreadsheets to load Table1 into Power Query, unpivot date columns, group by week, and load a refreshable weekly average table into Table2. The built-in Power Query workflow makes it easy to update results whenever source data changes.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, open the workbook containing Table1, and click anywhere inside the source table.
- 2. Load Table1 into Power Query: Go to the Data tab and choose Get Data > From Table/Range to open the Power Query Editor with the source table.
- 3. Unpivot and group by week: Select the date columns, unpivot them, add a week column, then use Group By with Average aggregations for each measure.
- 4. Load the result to Table2 and refresh: Choose Close & Load To, select Table2 or a new worksheet as the destination, and right-click the result table > Refresh after adding new source data.
Frequently Asked Questions
Can Power Query calculate weekly averages from multiple date columns?
Yes. Unpivot the date columns so each date becomes a row value, add a week column from the date value, then group by that week column and use Average aggregations for each measure.
How do I refresh the weekly average table after adding new rows?
Add the new rows to Table1, right-click any cell inside the result table and select Refresh, or go to Data > Refresh All in WPS Office Spreadsheets.
Why do I need to unpivot the date columns before grouping?
Power Query groups rows rather than columns. Unpivoting converts multiple date columns into rows so a single week group can aggregate all dates and measures correctly.
Will the result table update automatically when Table1 changes?
The result table does not update automatically by default. Add new source records and then refresh the query manually, or configure the connection refresh behavior if your version supports it.
Can I place the weekly average result on another worksheet?
Yes. When closing and loading the query, choose Close & Load To and select another worksheet or an existing table such as Table2 as the destination.
