Power Query Problems

Update a Master Excel Table with Weekly Data Using Power Query

Elise WilliamsElise Williams Nov 13, 2026 869 views

Question details

Combine weekly store extracts with existing master data and retain the newest visit for each store, category, and brand.

Product
Microsoft Excel Power Query
Device & OS
not provided
Scenario
Weekly CSV extracts contain raw store data that needs to update a master report while preserving store information, region order, and calculated results.
Observed behavior
The desired master table shows the latest available raw values for each store, category, and brand, with older visits retained only in the source history.
Before you start

Confirm that your Excel version provides Data > Get Data and that each source contains store, category, brand, and visit date fields. Identify the raw fields in columns F through Q separately from the later calculation columns, and decide how to resolve records with identical visit timestamps.

Solution 1Recommended

Build a refreshable master query that retains the latest visit for each store, category, and brand

Use this approach to combine historical master data with weekly extracts and generate a latest-visit master table without manually replacing individual cells.

Power Query produces a refreshed result table; it does not update arbitrary cells in the original source table in place. Keep the baseline master data separate from the query output, and retain weekly files or another cumulative history source so subsequent refreshes do not lose earlier updates.

This workflow selects the entire newest record for each store, category, and brand. If a newer extract contains blank fields, those blanks remain unless you explicitly add a separate rule for filling missing values.

1
Prepare the source tables

Select the existing master raw-data range and use Insert > Table, then give it a distinct name such as TableMasterBaseline under Table Design > Table Name. Create a separate TableShop table for store information, including a stable StoreID, region, and numeric RegionOrder. If the raw extract is already in the workbook, format it as a table named TableExtract.

2
Connect to the weekly extracts

For weekly CSV files, select Data > Get Data > From File > From Folder, choose a folder containing only the relevant extracts, and select Combine & Transform Data. Keep previous weekly files in this folder if they form your history. For a workbook table, select a cell in TableExtract and use Data > From Table/Range instead. In Power Query Editor, use Home > Manage Parameters > New Parameter to define a reusable folder or file path, then reference it in the source step.

3
Normalize fields and visit dates

In Power Query Editor, give matching fields consistent names across the baseline and extract queries. Select the visit column and choose Transform > Data Type > Date/Time; use Using Locale if the source date format requires it. Set StoreID to Text if leading zeros matter. If stores appear as separate column headers, select the identifier columns and use Transform > Unpivot Other Columns to create a consistent StoreID-and-value structure before combining the data.

4
Append the baseline and weekly history

Load TableMasterBaseline into Power Query using Data > From Table/Range. In Power Query Editor, select Home > Append Queries > Append Queries as New and append the normalized baseline query and weekly extract query. Name the combined query AllVisits. Confirm that StoreID, Category, Brand, and VisitDateTime are populated and consistently typed.

5
Find the newest visit for each key

Right-click AllVisits in the Queries pane and choose Reference. In the referenced query, select Home > Group By > Advanced, group by StoreID, Category, and Brand, and create a LatestVisit column using the Max operation on VisitDateTime. Name this query LatestVisits. This explicitly identifies the newest visit instead of relying on sorting followed by Remove Duplicates.

6
Retrieve the matching newest records

Select AllVisits and choose Home > Merge Queries > Merge Queries as New. Merge with LatestVisits using StoreID, Category, Brand, and VisitDateTime on the left, matched to StoreID, Category, Brand, and LatestVisit on the right, in that order. Choose Inner join to retain only matching newest records. If multiple records share the same newest timestamp, resolve them with a documented secondary key, such as an extract timestamp or unique visit ID, before proceeding.

7
Add store information and restore report order

Load TableShop with Data > From Table/Range, then merge it into the newest-record query using StoreID and a Left Outer join. Expand the required store fields, including RegionOrder, and sort RegionOrder ascending, followed by the desired store, category, and brand fields. If the report needs one column per store, select StoreID and use Transform > Pivot Column with the appropriate value field; ensure the remaining row identifiers make each pivoted value unique.

8
Load the result and refresh weekly

Select Home > Close & Load > Close & Load To and load the final query as a table on a separate master-output worksheet. Recreate calculated fields in Power Query with Add Column > Custom Column, or keep calculations in a separate report that references the output table. Add each new weekly extract to the source folder and select Data > Refresh All in Excel.

Review schema changes before refreshing: Changes to TableShop can flow through the query, but steps that explicitly select, expand, rename, or type columns may need updating. Use a stable StoreID for matching rather than a store name that may change, and keep the query output out of its own source folder.
Free Microsoft Office alternative

Review and Share Your Refreshed Master Report in WPS Spreadsheet

Use WPS Office as a free, lightweight alternative for reviewing and editing the Excel report produced by this workflow. Its familiar spreadsheet interface and support for Excel formats make it easy to move everyday reporting tasks to WPS. Build and refresh the Power Query pipeline in a supported version of Microsoft Excel; WPS is not a replacement for that refresh process.

Open and edit Microsoft Excel .xlsx and .xls spreadsheet files.Review the latest store, category, and brand results after refreshing the report in Excel.Use familiar spreadsheet tools for formulas, sorting, filtering, and report formatting.Move everyday spreadsheet review to a free, lightweight Office alternative.
microsoft office alternative - wps office

Frequently Asked Questions

Does Power Query overwrite the existing master table in place?

Not as a row-by-row update to its source table. Use Home > Close & Load To to load a separate result table. Keep the source baseline and query output separate to avoid a circular dependency, and use the output as the refreshed master report.

Why not just sort by visit date and remove duplicates?

Power Query does not guarantee which duplicate Table.Distinct will preserve in every evaluation. Use Home > Group By to calculate the maximum visit timestamp for each StoreID, Category, and Brand, then merge that result back to the full records. Resolve equal timestamps with an explicit secondary key.

Can I replace the weekly CSV with a new file each week?

Only if another source retains all the history you need, or the replacement file already contains the complete latest state. A query does not automatically remember its previous output. For incremental weekly extracts, use Data > Get Data > From File > From Folder and retain the earlier files so refresh can reconstruct the master.

What happens to formulas after the raw data columns?

Do not include the later calculation columns in the raw-data append unless they are intentionally part of the source. Add calculations through Power Query Editor > Add Column > Custom Column, or place them in a separate report referencing the loaded table. Check formula behavior after a refresh changes the number or order of rows.

Can a PivotTable create the final master report?

Yes, if the goal is a summary or reporting layout. Select the latest-record output and use Insert > PivotTable, then choose Design > Report Layout > Show in Tabular Form. Apply the latest-visit selection in Power Query first; a PivotTable alone does not choose the newest complete record for each store, category, and brand.