Power Query Problems

How to Transpose Filled Cells into New Rows in WPS Office Spreadsheet

Aamir Naveed AkramAamir Naveed Akram Nov 13, 2026 869 views

Question details

The user wants to transform a table in Excel 365 so that each populated cell in multiple columns creates a separate row, while the first-column value is repeated.

How to Transpose Filled Cells into New Rows in WPS Office Spreadsheet
Product
WPS Office Spreadsheet
Device & OS
not provided
Scenario
A source table has repeated row identifiers in the first column and filled values scattered across multiple columns. The user needs a normalized list with one record per filled cell.
Observed behavior
The current table keeps multiple filled values side-by-side in each row instead of converting each filled cell into its own row.
Before you start

Before transforming, identify the key column that must repeat for every new row and confirm whether blank cells should be dropped from the result. Remove confidential data if you plan to test the transformation on a sample table.

Solution 1Recommended

Unpivot Filled Columns with Power Query in WPS Office Spreadsheet

Use this method when you want a repeatable, refreshable transformation of a crosstab-style table into new rows.

Power Query, labeled Data Query in some WPS Office versions, normalizes the table by turning selected columns into attribute/value pairs. The original key column can be kept and repeated automatically for each populated cell.

1
Load the source range into Power Query

Click any cell in the source table, then go to the Data tab and choose From Table/Range. Confirm the table range and enable My table has headers if needed, then click OK.

2
Select the key column

In the Power Query Editor, select the column whose original value must repeat for each new row, such as Name, ID, or Product Code.

3
Unpivot other columns

Right-click the selected key column and choose Unpivot Other Columns. This converts every filled cell in the other columns into a new row under an Attribute and Value layout.

4
Remove or filter blank entries

If blank cells remain, click the filter arrow on the Value column, clear (Blank), and click OK.

5
Load the normalized result

Click Home > Close & Load in the Power Query Editor to place the new rows into a worksheet.

Unpivot Filled Columns with Power Query in WPS Office Spreadsheet
Filled cells are now rows: Each populated cell has its own row, and the selected key column is repeated for that row.
Direct WPS Office solution

Convert Filled Cells to New Rows with WPS Office Spreadsheet

WPS Office Spreadsheet gives you Power Query/Data Query tools to unpivot a crosstab table, so each filled cell becomes its own row while the key column repeats automatically. It works with Excel 365 files and saves back to .xlsx without losing format compatibility.

  1. 1. Open the workbook in WPS Office Spreadsheet: Launch WPS Office, click Menu > Open, and select the Excel 365 workbook that contains the table.
  2. 2. Load the source data into Power Query: Click inside the table, go to the Data tab, and choose From Table/Range to open the Power Query Editor.
  3. 3. Unpivot the filled columns: Select the repeated key column, right-click it, and choose Unpivot Other Columns. Filter out blank values from the Value column if they appear.
  4. 4. Close & Load the result: Click Home > Close & Load to send the normalized rows to a new worksheet.
  5. 5. Save the workbook as .xlsx: Press Ctrl+S or click Menu > Save As and choose Excel Workbook (*.xlsx) to keep the result compatible with Excel 365.
Free to download and use for Power Query-style unpivot transformationsReads and writes .xlsx, .xls, and CSV files for Excel 365 compatibilityFamiliar ribbon interface and Power Query workflow reduce migration effortLightweight, low-resource Office suite that runs on Windows, macOS, Linux, Android and iOS
microsoft office alternative - wps office

Frequently Asked Questions

Does WPS Office include Power Query for unpivoting columns?

Yes, recent WPS Office versions include Power Query/Data Query on the Data tab. Select the source range, click Data > From Table/Range, then right-click the key column and choose Unpivot Other Columns.

Will unpivoting create rows for empty cells?

Normally no. Power Query unpivots non-blank values. If blanks still appear, click the filter arrow on the Value column and clear (Blank) before loading.

Can I keep the original first-column value repeated in every new row?

Yes. In the Power Query Editor, select only the first-column key column, then right-click and choose Unpivot Other Columns. The key value is repeated automatically for each filled cell in that row.

Does this work when the source table has merged cells?

Merged cells can cause errors. Unmerge them and fill down the key column first so every source row has the repeated value before loading into Power Query.

Will the WPS result still open in Excel 365?

Yes. Save the output as a standard Excel Workbook (*.xlsx), and Excel 365 can open it without losing the normalized rows or formatting.