Power Query Problems

How to Convert Customer Orders into a Filtered List Using Power Query

Nimra Malik Sep 28, 2026 871 views

Question details

The user needs to transform a matrix-style customer order table into a flat list by unpivoting product columns and filtering out zero-quantity orders.

How to Convert Customer Orders into a Filtered List Using Power Query

Product: Spreadsheet
Device & OS: not provided
Scenario: Organizing daily delivery or bakery orders where a grid of customers and products needs to be converted into a row-by-row preparation list.
Observed behavior: A wide table with customers in rows and products in columns needs to be transformed into a concise three-column list (Customer, Product, Quantity) without zero entries.

Before you start

Ensure your source data is organized in a clear grid with distinct customer names in the first column and product names as headers, containing no blank rows inside the dataset.

Solution 1Recommended

Unpivot and Filter Columns in Power Query

Use the Unpivot Other Columns feature in Power Query to flatten your order matrix, then filter out empty or zero quantities to generate a clean list.

This method is highly efficient for recurring tasks. By setting up the query once, you can quickly refresh the data whenever new daily orders are added to the source table.

Convert Source to a Table

Select your original data range and press Ctrl+T to convert it into a Table. Name this table 'Table1' for easy identification.

Load Data into Power Query

Navigate to the Data tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.

Unpivot Product Columns

Right-click the header of the Customer column and select 'Unpivot Other Columns'. This action dynamically creates new 'Attribute' (Product) and 'Value' (Quantity) columns.

Filter Out Zero Quantities

Click the filter dropdown arrow on the newly created 'Value' column, uncheck '0' (and 'null' if present), and click OK to remove products with no orders.

Rename Columns and Load

Double-click the column headers to rename 'Attribute' to 'Product' and 'Value' to 'Quantity'. Finally, click 'Close & Load' to output the formatted list to a new worksheet.

Unpivot and Filter Columns in Power Query

Dynamic Updates: Once configured, you only need to right-click the output table and select 'Refresh' to process new orders automatically.

Free Microsoft Office alternative

Manage Your Spreadsheets Seamlessly with WPS Office

If you frequently deal with complex customer orders or find Excel's advanced data tools intimidating, try WPS Office. It is a lightweight, feature-rich suite that offers seamless compatibility with Microsoft Excel formats, allowing you to manage and filter your data efficiently.

1. Download WPS Office: Visit the official WPS website to download the free, lightweight installer.
2. Open Your Existing Spreadsheet: Launch WPS Spreadsheet and easily open your existing .xlsx order files.
3. Manage Data Easily: Utilize built-in data filtering, pivot tables, and sorting tools to organize your customer lists.

Fully compatible with Microsoft Office formats, including .xlsx files containing data structures.Lightweight installation and incredibly fast data processing speeds.Familiar user interface ensuring seamless migration with no steep learning curve.Free to use for organizing daily bakery, delivery, and personal order management.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my zero-quantity orders still showing up in the final list?

Ensure you explicitly filtered out '0' in the Values column dropdown inside the Power Query Editor. If your source data uses blanks instead of zeros, you also need to filter out 'null' values.

Can I add new customers to the source table and update the list?

Yes. Because you converted the source data into a Table before importing, any new rows or customers added will automatically be included. Just right-click your output list and select 'Refresh' to update.

What does the 'Unpivot Other Columns' feature actually do?

Unpivoting takes data stored horizontally across multiple columns (like individual product names) and transforms it vertically into rows, pairing each product with its corresponding customer to create a structured database format.

Does this Power Query method modify my original customer order data?

No. Power Query establishes a separate connection to your data. It applies the transformations virtually and outputs the resulting list into a completely new worksheet, leaving your original order grid untouched.

Excel & Data Problems