Parse Structured Message Headers in Excel Power Query
Question details
Extract selected fields from structured text messages containing headers such as 0xB821 and 0xB970, while correctly associating frequency-band values with available bitmap and priority fields.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- The user wants to transform message records into a structured table by filtering headers, parsing key-value fields, grouping related records, and pivoting selected values.
- Observed behavior
- The expected relationships are not consistent in the available records: fields such as ssb-PositionsInBurst, InOneGroup, groupPresence, ssb-ToMeasure, and CellReselectionPriority may be absent or may not occur in the same record group.
Review several complete message blocks before creating pivot logic. Confirm which identifier defines a single related record set, because a frequency-band value may not always include a bitmap or priority value.
Validate message-field relationships before building the Power Query transformation
Use this approach first when source records contain optional fields or when the relationship between frequency bands, bitmaps, and priorities is uncertain.
Do not assume that every freqBandIndicatorNR has a matching ssb-ToMeasure bitmap and CellReselectionPriority. For example, a frequency-band record can exist without either field, and ssb-PositionsInBurst may not be accompanied by InOneGroup or groupPresence in the same message set.
A representative sample containing complete message groups is required to determine a reliable grouping key and the correct conditions for filling or leaving values blank.
In the source text or Excel table, retain complete blocks around the 0xB821 and 0xB970 headers rather than copying only individual matching lines.
Inspect the parsed records for a stable message, cell, carrier, or sequence identifier. In Power Query, use Transform > Group By only after confirming that this identifier consistently joins related fields.
Create a list of fields that can be missing, including ssb-ToMeasure, CellReselectionPriority, InOneGroup, and groupPresence. Keep missing values as null instead of assigning them to the nearest frequency-band record.
Filter the parsed table for known exceptions such as freqBandIndicatorNR 258 and verify whether bitmap and priority values are truly absent before defining pivot rules.
Parse and pivot confirmed key-value message fields in Power Query
Use this method after the source structure and grouping rules have been verified with representative records.
Parse Structured Message Records with WPS Spreadsheet Power Query Tools
Use WPS Spreadsheet to load structured text records, isolate required message headers, split key-value fields, and organize validated data into a readable table. Apply grouping and conditional handling only after confirming how optional bitmap and priority fields relate to each message group.
- 1. Open the source data: In WPS Spreadsheet, open the .xlsx, .xls, or text-based source file and place the message records in a single worksheet column.
- 2. Filter relevant header records: Use Data > Filter and filter the message-text column for rows containing 0xB821 or 0xB970.
- 3. Split message fields: Select the filtered text column, choose Data > Text to Columns, and use the message delimiter to separate individual key-value fields.
- 4. Separate keys from values: Apply Data > Text to Columns again using the key-value separator, then label the resulting columns Key and Value.
- 5. Review associations before summarizing: Sort or filter by the confirmed message-group identifier and frequency-band field, leaving bitmap and priority cells empty when the source record does not supply them.
Frequently Asked Questions
Why does a frequency band not always have an ssb-ToMeasure bitmap or CellReselectionPriority?
These fields can be optional or can belong to a different message context. Preserve null values unless the source specification and a shared grouping identifier prove that the values belong to that frequency-band record.
Can I group Power Query records only by freqBandIndicatorNR?
Only if freqBandIndicatorNR uniquely identifies one complete related record set. If the same band appears in multiple messages, group by a message, cell, sequence, or other validated identifier as well.
How do I split message text into fields in Power Query?
Load the text through Data > From Table/Range, select the text column, and use Transform > Split Column > By Delimiter. Then split each field by its key-value separator to create Key and Value columns.
What should I do when ssb-PositionsInBurst exists without InOneGroup or groupPresence?
Treat the fields independently unless the message specification defines a direct dependency. Do not fill missing InOneGroup or groupPresence values from another row solely because ssb-PositionsInBurst is present.
Why is a sample file needed for this Power Query transformation?
A sample with complete message blocks reveals the actual delimiters, nesting, repeated fields, optional fields, and stable grouping identifier required to build reliable parsing and pivot rules.
