Power Query Problems

How to Merge Invoice Labels and Values Using Power Query

Nimra Malik Sep 29, 2026 870 views

Question details

The user needs to transform a complex invoice worksheet with scattered fields, hidden rows, and multiple records into a structured, automated monthly overview using Power Query.

How to Merge Invoice Labels and Values Using Power Query

Product: Excel / Power Query
Device & OS: not provided
Scenario: Transforming and cleaning complex invoice data layouts into a consolidated tabular overview.
Observed behavior: Data is scattered across the worksheet with fields in different locations and hidden rows, requiring structural transformation to merge labels and values into single rows per job.

Before you start

Ensure your raw invoice data is formatted as a Table or loaded properly into Power Query, and make a backup of your original dataset before applying structural transformations.

Solution 1Recommended

Use Fill Down and Pivot Column in Power Query

Transform scattered invoice labels and values into a clean dataset by filling down grouping identifiers and pivoting label columns.

To consolidate messy invoice data, you must first ensure each row is associated with a specific job identifier. Once irrelevant rows are removed, pivoting the label column will restructure the data into a clean, horizontal format.

Apply Fill Down

In the Power Query Editor, select the 'Job' and 'Mode' columns. Go to the Transform tab and click 'Fill' > 'Down' to associate each row with the correct job.

Clean Up Rows

Filter out null rows and remove any rows that do not contain the required charge labels by clicking the filter dropdown arrow on the column headers.

Pivot the Label Column

Select the 'Label' column, navigate to the Transform tab, and click 'Pivot Column'.

Assign Values

In the Pivot Column dialog box, select the 'Value' column under the Values Column dropdown and click OK. This converts labels like Destination and Weight into individual columns with one row per job.

Use Fill Down and Pivot Column in Power Query

Automation: Once set up, these steps are saved as a query. You can simply refresh the query next month when new data is added to automate the overview.

Free Microsoft Office alternative

Try WPS Office for Seamless Spreadsheet Data Management

While complex Power Query workflows are tailored to Microsoft Excel, WPS Office provides a lightweight, fast, and free alternative with robust data processing, pivot tables, and high compatibility with Excel files. It is an excellent choice for users looking to manage daily spreadsheet tasks efficiently without heavy resource usage.

1. Download and Install: Visit the official WPS Office website to download and install the free software suite.
2. Open Your Invoice File: Launch WPS Spreadsheet and open your existing .xlsx or .csv invoice dataset.
3. Use WPS Pivot Tables: Select your data range and go to Insert > PivotTable to summarize and analyze your monthly overview easily.

Fully compatible with Microsoft Excel (.xlsx, .xls) and CSV file formats.Includes powerful Pivot Tables and built-in functions for robust data cleanup.Free, lightweight, and loads quickly even with large datasets.Familiar user interface makes migrating from Microsoft Office seamless.

microsoft office alternative - wps office

Frequently Asked Questions

Why are some values missing after using Pivot Column in Power Query?

Missing values usually occur if the 'Values' column in the Pivot Column dialog is not set correctly, or if the aggregation function (like Count instead of Don't Aggregate) is misconfigured. Ensure you select 'Don't Aggregate' in the advanced options if you want the exact text or number values to appear.

What does Fill Down do in Power Query?

Fill Down replaces null or empty values in a column with the last non-empty value located above it. It is essential for filling in grouped identifiers, such as Invoice Numbers or Job IDs, across multiple rows before pivoting.

Can I automate this Power Query process for next month's invoices?

Yes. Power Query saves your transformation steps. When you receive a new monthly invoice file, simply replace the old data source or update the folder, then click 'Refresh All' in the Data tab to automatically apply the same merging and pivoting steps.

How do I deal with hidden rows and columns when importing invoice data?

Power Query typically ignores Excel's hidden row/column states and imports the raw data as is. You will need to explicitly filter out the irrelevant or blank rows within the Power Query Editor using the column filter dropdowns.

Excel & Data Problems