Split Structured Text into Columns with Excel Power Query
Question details
Import a consistently structured text file and convert repeated identifiers, reference signals, signal IDs, and type values into a tabular dataset.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to parse configuration-style text containing values such as tci-StateId, csi-rs or ssb reference signals, and qcl-Type.
- Observed behavior
- The required values are embedded in structured text rather than already arranged in separate spreadsheet columns.
Confirm that each record in the text file follows the same label order and delimiter pattern. Keep the text file in a stable location, because the Power Query Source step must point to its current file path.
Import the text file and update the Power Query source path
Use this method when the text file structure is consistent and you need a refreshable Excel query.
Power Query can load the text file, split it into records, and extract values by using the labels that surround each value. After the query is created, replacing the source path allows it to read the intended file.
In Excel, select Data > Get Data > From File > From Text/CSV, select the structured text file, and choose Transform Data.
In Power Query Editor, select Home > Advanced Editor or select the Source step under Applied Steps. Update the file path in File.Contents("C:\\path\\yourfile.txt") to the location of your text file.
Select the text column, then choose Transform > Split Column > By Delimiter. Use the repeated record boundary or line-break pattern that separates one tci-StateId block from the next.
Select Add Column > Custom Column and use Text.BetweenDelimiters to pull text after labels such as "tci-StateId " and before ",". Create separate custom columns for the reference signal label, its ID, and the value after "qcl-Type ".
Set each extracted identifier column to Whole Number or Text as appropriate, then select Home > Close & Load to place the results in an Excel table.
Create a custom Power Query transformation for repeating signal blocks
Use a custom M transformation when each record spans multiple lines or requires several dependent extractions.
Organize Parsed Text Results in WPS Spreadsheets
Power Query is a Microsoft Excel feature. If you need a free, lightweight office suite to open, review, and organize the resulting CSV or spreadsheet data, WPS Office provides a familiar spreadsheet workspace with seamless migration from Microsoft Office files.
Frequently Asked Questions
Where do I change the text file path in an existing Power Query?
In Excel Power Query Editor, select the Source step under Applied Steps. Edit the file location in the formula bar, or choose Home > Advanced Editor and update the path inside File.Contents().
Can Power Query extract a value between two text labels?
Yes. Select Add Column > Custom Column and use Text.BetweenDelimiters. For example, use the start label "tci-StateId " and the ending delimiter "," to return the ID value.
How can I separate csi-rs records from ssb records?
Create a conditional column from Add Column > Conditional Column. Test whether the record text contains "csi-rs" or "ssb", then return the matching signal type in a separate column.
Why do my extracted Power Query columns contain errors or null values?
This usually means some records do not include the expected label or delimiter. Filter the source rows first, verify the exact spacing and line breaks, and use try ... otherwise null in a custom column for optional fields.
Can I export parsed Power Query results to CSV?
Load the query output to an Excel table with Home > Close & Load. Then use File > Save As and select CSV UTF-8 (Comma delimited) to create a CSV file.
