Convert Paired Dependency Columns into Rows in WPS Office Excel Power Query
Question details
The user wants to transform a wide Excel table with repeated Dependency and Dependency Date column pairs into a normalized table that stores one dependency per row while retaining the project name.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user has a wide table with multiple paired dependency and dependency-date columns and needs a normalized one-row-per-dependency layout for filtering, analysis, or reporting.
- Observed behavior
- The source table stores each dependency and its date in repeated column pairs, making it difficult to filter, summarize, or relate dependencies by project without manual splitting.
Open the workbook in WPS Office Excel and confirm that the table has a Project column plus repeated pairs of Dependency and Dependency Date columns with consistent names, such as Dependency1, Dependency Date1, Dependency2, and Dependency Date2.
Unpivot paired columns and pivot attribute names in Power Query
Use this method when you want a repeatable Power Query transformation that converts multiple dependency column pairs into one row per project-dependency combination.
Power Query can unpivot all dependency-related columns at once, separate the attribute name from its sequence number, and then pivot the attribute portion into dedicated Dependency and Dependency Date columns.
In WPS Office Excel, click the Data tab, select the table or range that contains the Project and paired Dependency/Dependency Date columns, and choose From Table/Range to open Power Query Editor.
Select the Project column, then go to Transform and choose Unpivot Other Columns. This creates Attribute and Value columns with one row for each project-column pair.
Select the Attribute column, click Split Column, and split by character transition from non-digit to digit so names such as Dependency1 and Dependency Date1 become the label and the sequence number in separate columns.
Select the label column that contains Dependency or Dependency Date, click Transform > Pivot Column, set Values Column to Value, and under Advanced Options choose Don't Aggregate. This creates Dependency and Dependency Date columns.
Filter the Dependency column to remove null or blank values, select the Dependency Date column, set its Data Type to Date, and click Close & Load to return the normalized table to WPS Office Excel.
Use an advanced M script to combine paired dependency columns
Use this method if you prefer an M code query or need to integrate the transformation into an existing scripted Power Query solution.
Normalize paired dependency columns in WPS Office Excel with Power Query
Use WPS Office Excel Power Query to unpivot, split, pivot, and clean repeated Dependency and Dependency Date column pairs so every project-dependency pair appears on its own row.
- 1. Open your workbook: Launch WPS Office Excel and open the workbook that contains the wide Project, Dependency, and Dependency Date columns.
- 2. Load the table into Power Query: Select the data range, go to the Data tab, and click From Table/Range to open Power Query Editor.
- 3. Apply the unpivot-split-pivot transformation: Keep Project, choose Unpivot Other Columns, split the Attribute name from its sequence number, pivot the label portion, and set Dependency Date as Date type.
- 4. Load the normalized result: Click Close & Load to return the one-row-per-dependency table to a new worksheet.
Frequently Asked Questions
Why do I need to split the Attribute column after unpivoting?
After unpivot, attribute names store both the column label and the sequence number, such as Dependency1 and Dependency Date1. Splitting them lets Power Query pivot the label part into separate Dependency and Dependency Date columns while using the number to keep each pair together.
What should I do if my dependency columns use different naming patterns?
Make the names consistent before loading the query, for example Dependency1, Dependency Date1, Dependency2, Dependency Date2. If names vary, adjust the split step or rename columns in Power Query Editor before pivoting.
Can I keep rows with blank dependencies?
You can keep them, but the accepted transformation removes blank dependencies to produce one row only for actual project-dependency pairs. If you need blank rows for completeness, skip the filter step or replace blanks after loading.
Does this work in WPS Office Excel like in Microsoft Excel Power Query?
Yes. WPS Office Excel includes a Power Query-compatible editor, so you can use the same unpivot-split-pivot workflow and open or save standard .xlsx files.
