Combine Multi-Line Customer Records in Excel with Power Query
Question details
Combine customer information spread across multiple rows in a CSV or TXT export into one row per record.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user wants to sort and analyze customer data exported across several rows.
- Observed behavior
- Each customer record should have its identifying fields and other details consolidated into a single row.
Inspect a representative excerpt of the file to identify its delimiter, customer or record markers, and field labels. Check whether each record has a unique identifier and whether any field occurs more than once within a record.
Fill down identifiers and pivot customer fields in Power Query
Use this method when each record has an identifiable starting row and its details can be arranged as field-name and value pairs.
The exact transformations depend on the export layout. A pivot requires one value per field for each record; repeated field values need to be combined or distinguished first.
In Excel, select Data > From Text/CSV, choose the export, check the delimiter in the preview, and select Transform Data.
In Power Query Editor, locate the customer or record identifier column. Ensure each new record has a unique marker, then select that column and choose Transform > Fill > Down to assign it to the following detail rows.
Use the Transform tab to split or reshape the data into columns for the record identifier, field name, and field value. Remove unrelated columns that would prevent rows from grouping together, and filter out rows that are not customer fields.
Select the field-name column and choose Transform > Pivot Column. Select the field-value column under Values Column and, under Advanced options, choose Don't Aggregate when each field has only one value per record.
Select Home > Close & Load, then check that each record appears on one row and that its values are in the correct columns.

Review Customer Spreadsheets with WPS Office
WPS Office is a free, lightweight Office alternative with a familiar interface for reviewing and editing customer spreadsheets. It supports common Excel formats for an easier move between office suites, though it does not fix Excel Power Query itself.

Frequently Asked Questions
How do I import a TXT file into Power Query?
In Excel, select Data > From Text/CSV, choose the TXT file, confirm its delimiter in the preview, and select Transform Data.
What if a customer record has no identifier on its first row?
Identify another reliable record-start marker in Power Query and create a unique record key before using Transform > Fill > Down. Without a reliable boundary, rows may be assigned to the wrong record.
Why does Pivot Column report duplicate values?
A record may contain more than one value for the same field. In Power Query Editor, inspect those rows and combine the values or create distinct field names before choosing Transform > Pivot Column.
Can I use the same query for a new export?
If the new file has the same structure and is available at the query's source location, use Data > Refresh All in Excel. If the structure or location changes, edit the query's source or transformation steps.




