Power Query Problems

How to Use Power Query to Sort People and Associated Numbers

Natalie Taylor Oct 21, 2026 868 views

Question details

The user needs to extract multiple people's names stored in a single cell, match them with their associated numbers, and create a structured table organized by person.

How to Use Power Query to Sort People and Associated Numbers

Product: Excel
Device & OS: not provided
Scenario: Reorganizing messy datasets where multiple people and numbers are combined in single cells, requiring a structured layout for each person.
Observed behavior: The goal state is a cleanly pivoted data table where each person has their own dedicated column or row listing all their associated numbers.

Before you start

Ensure your dataset is formatted as a Table (Ctrl+T) before importing it into Power Query, and identify the exact delimiter (such as a comma, space, or line break) separating the names.

Solution 1Recommended

Split and Pivot Data using Power Query Editor

Use Power Query's built-in split, group, and pivot features to automatically restructure combined cell data into individual columns per person.

By utilizing Power Query, you can automate the process of separating grouped names and matching them to their corresponding numbers. This is especially helpful if the cells in your Person column contain line breaks or custom delimiters.

Load Data into Power Query

Select your data range and click Data > From Table/Range on the ribbon to open the Power Query Editor.

Split the Column by Delimiter

Select the Person column, go to Home > Split Column > By Delimiter. Choose your delimiter (for line breaks, choose Custom and type #(lf)) and under Advanced Options, select to split into Rows.

Add an Index Column

Navigate to the Add Column tab and click Index Column > From 1. This step is crucial to prevent aggregation errors when dealing with duplicate numbers.

Pivot the Data

Select your Person column, go to Transform > Pivot Column. In the Values Column dropdown, select your Numbers column. Open Advanced options and select 'Don't Aggregate', then click OK.

Load the Restructured Table

Click Home > Close & Load to output your freshly sorted and pivoted table back into a new worksheet.

Split and Pivot Data using Power Query Editor

Refreshing Data: Once your query is set up, any new rows added to the original table can be processed instantly by clicking Data > Refresh All.

Free Microsoft Office alternative

Looking for a Lightweight Tool for Complex Data Sorting?

While Power Query is a specific feature of Microsoft Excel, WPS Office provides a free, highly compatible, and user-friendly alternative for managing your spreadsheets. Easily split text, sort data, and create pivot tables to manage complex records without writing advanced code.

1. Download and Install: Get WPS Office for free from the official website and install it on your device.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel (.xlsx) files directly with zero format loss.
3. Manage Data Easily: Use the Data tab to access Text to Columns, sorting tools, and Pivot Tables to organize your records seamlessly.

Free and lightweight office suite for seamless data managementFully compatible with Microsoft Excel (.xlsx) formats and featuresBuilt-in Text to Columns and Pivot Table features for easy data restructuringFamiliar user interface with zero learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle line breaks inside a cell when splitting data in Power Query?

When using the 'Split Column by Delimiter' feature in Power Query, choose 'Custom' as your delimiter and type '#(lf)' (without quotes). This M-code syntax specifically targets line feeds (line breaks) within cells.

Why does Table.Pivot result in errors for duplicate values?

If multiple identical numbers are associated with the same person, pivoting without an index can cause aggregation errors. Adding an Index Column before pivoting ensures that every row has a unique identifier, allowing 'Don't Aggregate' to function correctly.

How do I update my pivoted table when I add new names or numbers?

You do not need to rebuild the query from scratch. Simply add your new data to the original source table, navigate to the Data tab in your workbook, and click 'Refresh All' to automatically apply the splitting and pivoting steps to the new data.

Excel & Data Problems