Power Query Problems

How to Combine and Clean Contract Note Excel Sheets using Power Query

Maira Mehtab Sep 20, 2026 868 views

Question details

The user needs to extract transaction data from multiple Excel contract note sheets, remove unnecessary headers, footers, and text, and consolidate the cleaned data into a single table.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Combining and cleaning multi-sheet financial transaction records (contract notes) for pivot-table analysis.
Observed behavior: The goal state is a single, clean table containing Contract Note No, Trade Date, Security, Buy/Sell, Quantity, Gross Rate, Net Total, and Taxes.

Before you start

Ensure all your contract note Excel sheets are stored in a single designated folder and follow a consistent structural layout so Power Query can combine them accurately.

Solution 1Recommended

Build a Power Query Transformation Process

Import all relevant sheets from a folder, remove non-transaction text, and append the data into a single analysis-ready table.

By utilizing the 'Get Data from Folder' feature, Power Query can automatically loop through all contract note files. Once imported, you can apply cleaning steps like removing nulls, filtering out repeated headers, and deleting footer rows before loading the final dataset.

Import data from folder

Open Excel, navigate to the 'Data' tab on the ribbon, click 'Get Data', select 'From File', and choose 'From Folder'. Browse to and select the folder containing your contract note sheets, then click 'Open'.

Transform data

In the preview window, click 'Transform Data' to launch the Power Query Editor. Expand the binary files by clicking the double-arrow icon located on the header of the 'Content' column.

Remove irrelevant text and footers

Use the filter drop-down on your primary columns (like 'Contract Note No' or 'Security') to uncheck '(null)' or blank values. To remove footers and disclaimers, go to the 'Home' tab, click 'Remove Rows', and utilize 'Remove Bottom Rows' or apply text filters to exclude specific disclaimer keywords.

Promote headers

If your actual column names are now sitting in the first row of your cleaned data, click 'Use First Row as Headers' in the 'Home' tab. This will properly assign fields like Trade Date, Buy/Sell, Quantity, and Gross Rate.

Validate and load data

Ensure all expected columns are formatted properly (e.g., Currency for Net Total and Taxes). Finally, click 'Close & Load' in the top-left corner to output the consolidated transaction table into a new worksheet for your PivotTable analysis.

Data Validation: Always validate your final query output against a dummy sample to ensure that taxes, net totals, and transaction dates from all individual sheets are successfully captured without truncation.

Combine Sheets Easily with WPS

Consolidate Multiple Contract Note Sheets in WPS Spreadsheet

WPS Office simplifies complex data consolidation. Instead of building advanced queries, you can use the built-in smart merging tools in WPS Spreadsheet to quickly combine multiple contract notes into a single table for immediate pivot-table analysis.

1. Open WPS Spreadsheet: Launch WPS Office on your device and open a new blank spreadsheet to act as your master file.
2. Access the Merge Tool: Navigate to the 'Data' tab on the top ribbon and click on 'Merge Workbooks' or 'Consolidate' depending on your specific version.
3. Add Contract Note Files: Click 'Add Files' in the dialogue box and select all the Excel contract note files you wish to combine from your computer.
4. Configure Merge Settings: Choose the option to merge multiple worksheets into a single worksheet. Specify the starting row of your actual data to automatically bypass overarching file headers.
5. Clean and Analyze: Once the data is merged into one sheet, use the standard AutoFilter function to uncheck and delete remaining footer rows, then insert a PivotTable to summarize your Gross Rate and Taxes.

Fully compatible with Microsoft Excel (.xlsx) file formatsBuilt-in 'Merge Workbooks' smart tool for instant data consolidationIntuitive interface with highly responsive PivotTable capabilitiesFree and lightweight software that runs smoothly on all devices

Frequently Asked Questions

Why are my headers repeating when I combine contract note sheets in Power Query?

This happens when you append multiple sheets without filtering out the header rows from the newly appended files. You can fix this in the Power Query Editor by clicking the filter drop-down on your main column and unchecking the header name (e.g., unchecking the word 'Contract Note No').

How do I remove irrelevant text like disclaimers from the bottom of my contract notes?

In Power Query, you can use the 'Remove Bottom Rows' feature located under 'Remove Rows' if the footer always consists of a fixed number of rows. Alternatively, apply a 'Text Filter' on your primary column to exclude rows that 'Contain' specific disclaimer phrases.

Can I use the combined Power Query table directly for a Pivot Table?

Yes, you can bypass loading the data into a standard worksheet. When you are ready to export, click the arrow below 'Close & Load', select 'Close & Load To...', and choose 'PivotTable Report'. This creates a direct connection between your query and the PivotTable.

Excel & Data Problems