Power Query Problems

How to Create Customer Labels Ignoring Blank Cells using Power Query in Excel

Maira Mehtab Sep 27, 2026 871 views

Question details

The user needs to create customer product lists and labels from a sales table while excluding any products that have blank quantities.

Product: Excel
Device & OS: not provided
Scenario: Generating clean customer product labels from a sales data table that contains empty quantity cells.
Observed behavior: The user wants to generate a continuous list or PivotTable grouped by customer and product that completely ignores and filters out blank quantity cells.

Before you start

Ensure your original sales data is formatted as an Official Excel Table (by pressing Ctrl+T) so that Power Query can easily load and refresh the data dynamically when updates occur.

Solution 1Recommended

Use Power Query to Unpivot Columns and Filter Blank Quantities

Transform your dataset by unpivoting the product columns, which allows you to effortlessly filter out blank quantities and generate clean data for customer labels or PivotTables.

Unpivoting data changes it from a wide format to a long format, consolidating multiple product columns into a single column. This is the most efficient way to handle empty cells across various product categories in a sales table.

Load Data into Power Query

Select any cell within your sales data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Unpivot Other Columns

Right-click the header of the customer column (or the first column containing customer names) and select 'Unpivot Other Columns' from the context menu.

Rename the Generated Columns

Double-click the newly created 'Attribute' and 'Value' column headers to rename them to more descriptive titles like 'Product' and 'Quantity'.

Filter Out Blank Quantities

Click the filter dropdown arrow on the new 'Quantity' column, uncheck '(Blank)' or 'null' from the list, and click OK to exclude products with no quantities.

Load Data and Create PivotTable

Click 'Close & Load' in the upper-left corner to return the transformed data to Excel. You can now use this resulting table to create labels or a PivotTable grouped by customer and product.

Dynamic Updates: When your original sales data changes, you do not need to repeat these steps. Simply right-click anywhere in your output table or PivotTable and select 'Refresh' to apply the Power Query rules automatically.

Manage Data with WPS Spreadsheet

Filter Data and Create Grouped PivotTables in WPS Office

WPS Spreadsheet provides powerful and intuitive tools to manage sales tables, filter out blank quantities, and group data seamlessly using PivotTables without needing advanced query knowledge.

1. Open Your Data File: Launch WPS Spreadsheet and open your workbook containing the sales data.
2. Apply Data Filters: Highlight your dataset, navigate to the Data tab, and click the 'Filter' button. Use the dropdown arrows on your quantity columns to uncheck '(Blanks)'.
3. Insert a PivotTable: Select the filtered data range, go to the Insert tab, click 'PivotTable', and place it on a new worksheet.
4. Group by Customer and Product: In the PivotTable fields list on the right side, drag the Customer field to the Rows area and place the Product field right below it to generate a clean, grouped list for labels.

Fully compatible with Microsoft Excel (.xlsx, .xls) file formatsFree and lightweight with a familiar, zero-learning-curve interfaceBuilt-in advanced AutoFilter and powerful PivotTable tools for quick data processing

microsoft office alternative - wps office

Frequently Asked Questions

What does 'Unpivot Other Columns' do in Power Query?

It transforms multiple columns of data into attribute-value pairs, converting a wide table format into a narrow, tabular layout that is highly suitable for PivotTables, reporting, and filtering out empty cells.

Can I use Power Query to filter out blank cells across multiple columns?

Yes. By unpivoting those columns first, all the scattered values are consolidated into a single 'Value' column, making it incredibly easy to filter out blanks or nulls in one single step.

Why is my Excel PivotTable still showing blanks after applying Power Query?

If your PivotTable displays blanks, it is likely loading from cached data. Ensure you have refreshed both the Power Query output and the PivotTable itself. Right-click the PivotTable and select 'Refresh' to pull the latest cleaned data.

Excel & Data Problems