How to Fill Missing Power Query Values by Identifier in WPS Office Excel
Question details
The user needs to fill empty cells in related rows that share the same identifier in Power Query, using existing populated values from rows with that identifier.
- Product
- WPS Office Excel - Power Query
- Device & OS
- not provided
- Scenario
- The user has a table with multiple rows per identifier and missing values, and wants to carry populated values into empty cells across all rows that share that identifier.
- Observed behavior
- Rows sharing the same identifier contain blank cells where other related rows already have values, so the data is not consistent across related rows.
Open the Power Query Editor and confirm which column is the identifier. Ensure empty cells are stored as null instead of blank text; otherwise Fill Up or Fill Down will not fill them.
Fill Missing Values Within Each Identifier Using Group By and Fill Up
Use this when each identifier can appear in multiple rows and values should be repeated only within rows that share the same identifier.
A direct Fill Up on the whole column can copy values across different identifier groups when rows are sorted incorrectly or contain gaps. Grouping by identifier first keeps the fill operation inside each related set of rows.
In WPS Office Excel, select your table and click Data > Get Data > From Table/Range to open the Power Query Editor.
Select the value columns you want to fill, click Transform > Replace Values, leave Value To Find blank, type null in Replace With, and click OK.
Click the filter arrow on the identifier column, clear the (Blank) checkbox, and click OK.
Select the identifier column, click Home > Group By, keep the identifier as the grouping column, name the new column Data, choose Operation: All Rows, and click OK.
Click Add Column > Custom Column, name it FilledData, enter the formula Table.FillUp([Data], {"ValueColumn1","ValueColumn2"}) with your actual value column names, and click OK.
Remove the original Data column, click the expand icon on FilledData, select the value columns to expand, and click File > Close & Load.
Use Group By to Consolidate One Row Per Identifier
Choose this if you do not need to keep every row and instead want a single complete row for each identifier.
Fill Missing Power Query Values by Identifier in WPS Office
In WPS Office Excel, open Power Query, replace blank cells with null, group rows by identifier, and use Table.FillUp within each group to carry existing values into empty related rows. Then load a clean table that remains compatible with Microsoft Excel.
- 1. Open Power Query: In WPS Office Excel, click Data > Get Data > From Table/Range to open your table in the Power Query Editor.
- 2. Convert blank cells to null: Select the value columns, click Transform > Replace Values, leave Value To Find blank, enter null in Replace With, and click OK.
- 3. Group by identifier: Select the identifier column, click Home > Group By, choose Operation: All Rows, and name the new column Data.
- 4. Fill inside each group: Click Add Column > Custom Column and use the formula Table.FillUp([Data], {"ValueColumn1","ValueColumn2"}) with your actual value column names.
- 5. Expand and load the table: Remove the Data column, expand the custom column with the filled values, and click File > Close & Load to return the result to your worksheet.
Frequently Asked Questions
Why does Fill Up not work when my cells look empty?
The cells likely contain empty text instead of null. In Power Query, select the affected columns, click Transform > Replace Values, leave Value To Find blank, enter null in Replace With, and then apply Fill Up.
How do I prevent Fill Up from copying values from the next identifier?
Group rows by the identifier column using Home > Group By with Operation: All Rows, and apply Table.FillUp inside each grouped table. This keeps values within the same identifier group instead of filling across the whole column.
Can I create one consolidated row per identifier instead of filling every row?
Yes. In Power Query, group by the identifier with Operation: All Rows, add a custom column that returns Table.First(Table.FillUp([Data], {"ValueColumn1","ValueColumn2"})), then expand that column and load the result.
Does this Power Query method work with Microsoft Excel files?
Yes. WPS Office can open .xlsx, .xls, and .csv files, apply the Power Query steps, and save or load results that remain compatible with Microsoft Excel.
