Power Query Problems

How to Create a List from Multiple Excel Columns with Power Query

WPS Content Manager Oct 10, 2026 869 views

Question details

The user needs to transform rows of event values spread across multiple columns into a single consolidated list, while excluding any blank cells to display each valid event-value combination.

How to Create a List from Multiple Excel Columns with Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Consolidating and reshaping multi-column event data into a flattened, vertical list format.
Observed behavior: The user has horizontal rows with scattered values and blanks, and needs them converted into clean, separate vertical records (e.g., A-B, A-C) without manual copying.

Before you start

Ensure your Excel data is formatted as an official Table (select your data and press Ctrl+T) before importing it into Power Query. This guarantees that your query will automatically include new rows or columns added in the future.

Solution 1Recommended

Use Unpivot Other Columns in Power Query

This is the most efficient and scalable method to flatten multiple columns into a single list while automatically dropping null values.

The 'Unpivot Other Columns' feature translates your horizontal data columns into vertical rows. By selecting your primary identifier column and unpivoting the rest, Power Query automatically generates 'Attribute' (former column headers) and 'Value' (the cell data) columns.

Load Data into Power Query

Select any cell inside your Excel table. Go to the 'Data' tab on the ribbon and click 'From Table/Range' to open the Power Query Editor.

Select the Identifying Column

In the Power Query Editor, locate the column (or columns) that acts as the unique identifier for each row (e.g., an ID or Name column). Click on its header to select it.

Unpivot the Remaining Columns

Right-click the header of your selected identifying column and choose 'Unpivot Other Columns' from the context menu. This condenses all unselected columns into two new columns: 'Attribute' and 'Value'.

Filter Out Blank Values

Click the filter drop-down arrow on the header of the newly created 'Value' column. Uncheck 'null' or '(Blank)' from the list and click 'OK' to remove any empty records.

Load the Data Back to Excel

Click 'Close & Load' in the top-left corner of the Home tab. The transformed, unpivoted list will be exported into a new worksheet in your Excel workbook.

Use Unpivot Other Columns in Power Query

Automatic Null Removal: In most cases, the Unpivot operation in Power Query automatically excludes null values during the transformation, saving you the extra step of filtering them out manually.

Free Microsoft Office alternative

Manage Your Spreadsheets Efficiently with WPS Office

If you are managing complex datasets and need a fast, reliable spreadsheet tool, WPS Office is an excellent, lightweight alternative to Microsoft Excel. It provides powerful data processing capabilities, pivot tables, and seamless compatibility with all your existing Excel files.

1. Download and Install: Get WPS Office from the official website and install it on your computer.
2. Open Your Data: Launch WPS Spreadsheet and easily open your existing Excel workbooks without format loss.
3. Analyze and Organize: Use powerful built-in functions like PivotTables and Data Consolidation to transform your lists.

Free and lightweight spreadsheet software with fast load timesHigh compatibility with Microsoft Excel (.xlsx) formatsFamiliar user interface allowing for a seamless migrationBuilt-in data consolidation, advanced formulas, and pivot table tools

microsoft office alternative - wps office

Frequently Asked Questions

What exactly does 'Unpivot' mean in Excel Power Query?

Unpivoting takes data that is spread horizontally across multiple columns and transforms it into a vertical layout. It pairs your existing row identifiers with the corresponding column headers and cell values to create a flat, standardized list.

Will my Power Query list update automatically when I change the original data?

The list will not update instantly as you type. To apply changes, you must refresh the query. You can do this by right-clicking anywhere inside the output table and selecting 'Refresh', or by clicking 'Refresh All' on the Data tab.

Why do I need to format my data as a Table before using Power Query?

Formatting your data as an official Excel Table (using Ctrl+T) makes the data range dynamic. This ensures that if you add new columns or rows to your source data later, Power Query will automatically detect and include them the next time you refresh.

Can I unpivot multiple specific columns instead of 'Other Columns'?

Yes. If you only want to unpivot certain columns, hold down the Ctrl key, click the headers of the specific columns you want to transform, right-click one of the selected headers, and choose 'Unpivot Columns'.

Excel & Data Problems