Power Query Problems

Find Earliest and Latest Non-Blank Product Values in Excel

Khadija Khan Sep 29, 2026 869 views

Question details

The user needs to retrieve the first and last non-blank values for various products based on chronological order (earliest and latest dates).

How to Extract Earliest and Latest Non-Blank Product Values by Date in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing data analysis where starting and ending product metrics over time must be extracted from a dataset that contains blank entries.
Observed behavior: The goal is to accurately filter out empty cells and identify the exact values corresponding to the minimum and maximum dates for each distinct product.

Before you start

Ensure your source data is formatted as an Excel Table (Ctrl+T) and that the date column contains valid date formats rather than text strings before applying queries or array formulas.

Solution 1Recommended

Use Power Query to Unpivot, Group, and Sort Data

Power Query provides a robust, repeatable way to reshape the dataset, ignore blank values, and systematically extract the earliest and latest values for each product.

By unpivoting the product columns against the date column, you create a flat dataset. This allows you to easily filter out empty values and group the data by product to find the extreme dates.

Load Data into Power Query

Click anywhere inside your data table, navigate to the Data tab on the ribbon, and select 'From Table/Range' to open the Power Query Editor.

Unpivot Product Columns

Select the 'Date' column. Go to the Transform tab, click the dropdown arrow under 'Unpivot Columns', and choose 'Unpivot Other Columns'. This creates an 'Attribute' (Product) and 'Value' column.

Filter Out Blank Values

Click the filter arrow on the new 'Value' column header. Uncheck both 'null' and any blank/empty strings to ensure only populated values remain.

Group by Product and Extract Values

Go to the Home tab and click 'Group By'. Group by your Product column. In the advanced settings, create two custom aggregations. You will need to edit the M code in the formula bar to sort the grouped tables: use `Table.Sort(_, {{"Date", Order.Ascending}}){0}[Value]` for the Earliest value, and `Order.Descending` for the Latest value.

Use Power Query to Unpivot, Group, and Sort Data

Automated Updates: Once the query is set up and loaded back to the worksheet, you can easily include new data by clicking 'Refresh All' on the Data tab.

Solution 2

Use Dynamic Array Formulas (LET, FILTER, SORT)

For users on newer versions of Excel, dynamic array functions can extract these values directly in the spreadsheet grid without opening Power Query.

Free Microsoft Office alternative

Handle Complex Data Analysis Easily with WPS Office

If you are dealing with complex data extraction and need powerful spreadsheet functions without a hefty subscription, WPS Office is a highly compatible, free alternative to Microsoft Office. It fully supports advanced dynamic arrays like FILTER and SORT, making data manipulation a breeze.

1. Download and Install: Get the free WPS Office suite from the official website and install it on your device in minutes.
2. Open Your Excel File: Launch WPS Spreadsheets and open your existing .xlsx file directly without any format conversion.
3. Apply Advanced Formulas: Use advanced array formulas like FILTER and SORT seamlessly, just as you would in Microsoft Excel.

Free to use with a lightweight and fast installation processHighly compatible with Microsoft Excel formulas and .xlsx file formatsNatively supports advanced dynamic array functions like FILTER, SORT, and LETFamiliar user interface ensuring a seamless migration from Excel

microsoft office alternative - wps office

Frequently Asked Questions

Why are my blank cells not being ignored in Power Query?

Blank cells might contain empty text strings ("") or hidden spaces rather than being recognized as true 'null' values by Power Query. To fix this, click the filter dropdown on your Value column and ensure you uncheck both 'null' and the blank option.

Can I get the earliest and latest values using standard formulas in older Excel versions?

Yes, you can use legacy array formulas combining INDEX, MATCH, MIN, MAX, and IF statements (entered with Ctrl+Shift+Enter). However, dynamic array functions like FILTER and SORT in newer versions, or using Power Query, are much more efficient and easier to maintain.

Will the Power Query method update automatically when I add new data?

Power Query does not update in real-time as you type. After adding new data to your source table, you must right-click the Power Query output table and select 'Refresh', or go to the Data tab and click 'Refresh All' to see the updated earliest and latest values.

Excel & Data Problems