Power Query Split Column into Rows: Keep IDs, Order, and Empty-Token Rules

Choose occurrences or unique relationships

This example is for Excel on Windows with Power Query with local Table TicketTags. Each source row represents one ticket, and the ticket_id values are unique in this sample. The tag field uses a semicolon as a list separator. Semicolons are not allowed inside an individual tag, and the source has no quoting or escaping convention for literal separators.

Our output represents each nonempty tag occurrence. T1 has red, blue, and red again, so the duplicate red must remain. If your reporting question is how many tickets carry red, create a separate unique ticket-tag result after the occurrence table. Counting occurrence rows answers a different question and would count T1 twice for red. Keep the two outputs named clearly so later charts use the intended one.

Import and use the Rows option

Create an Excel Table named TicketTags from ticket-tags-input.csv, then use Data > From Table/Range. Set ticket_id and tags to Text. Inspect the delimiter inside the tags field; it is a semicolon even though the outer CSV file is separated by commas. These are two distinct parsing stages.

1. Select tags and choose Transform > Split Column > By Delimiter. Use Custom if necessary and enter a semicolon.
2. Select Each occurrence of the delimiter. Open Advanced options and choose Rows, then confirm. Choosing Columns would instead produce a growing set of tag columns.
3. Select the split tag field and choose Transform > Format > Trim. This example removes leading and trailing whitespace from individual tokens.
4. Filter the resulting field to exclude null and empty text according to the declared no-tag rule. Retain the parent ticket_id alongside every surviving token.
5. Rename the token column tag and load a new worksheet. This menu workflow creates the relationship rows; use the complete query below if original token positions are required.

Decide what happens to empty tokens and empty parents

T2 contains green followed by two separators, which describes green plus two empty token positions. The report keeps only meaningful tags, so those empty tokens are removed. T3 has no tag field value and produces no child record. Preserve the original ticket Table if another report must still count all four tickets, including tickets without tags.

Do not fill an empty tag with the preceding tag. That would create extra classifications that the source never supplied. If an empty token signals a malformed export rather than an acceptable omission, keep an exception report before filtering. The attached token-audit.txt gives the expected empty-token counts for this particular sample; no universal empty-token policy is implied.

Preserve token positions with the complete query

The query uses Text.Split for the simple semicolon convention, constructs a position before filtering, and expands each remaining record. tag_position therefore records the original token slot, not a new consecutive sequence after empty values are discarded. A value such as red;;blue would produce positions 1 and 3. That makes gaps visible when the source list needs auditing. It removes empty token lists before expansion so the no-tag policy does not depend on how the engine represents an expanded empty list.

Create the TicketTags source Table first. In a blank query, open Advanced Editor and insert the following code. It keeps ticket_id unchanged and sorts the output by ticket_id and original position for the downloadable expected result. If the input later contains repeated parent ticket IDs, add a source-row identifier too; ticket_id plus token position would no longer uniquely identify an occurrence.

let
    Source = Excel.CurrentWorkbook(){[Name="TicketTags"]}[Content],
    Typed = Table.TransformColumnTypes(Source, {{"ticket_id", type text}, {"tags", type text}}),
    WithRecords = Table.AddColumn(Typed, "tokens", each
        let
            Parts = if [tags] = null then {} else Text.Split([tags], ";"),
            Records = List.Transform(List.Positions(Parts), (i) => [tag_position = i + 1, tag = Text.Trim(Parts{i})])
        in List.Select(Records, each [tag] <> "")
    ),
    NonemptyParents = Table.SelectRows(WithRecords, each List.Count([tokens]) > 0),
    ExpandedList = Table.ExpandListColumn(NonemptyParents, "tokens"),
    ExpandedRecord = Table.ExpandRecordColumn(ExpandedList, "tokens", {"tag_position", "tag"}),
    Output = Table.SelectColumns(ExpandedRecord, {"ticket_id", "tag_position", "tag"}),
    OutputTypes = Table.TransformColumnTypes(Output, {{"tag_position", Int64.Type}, {"tag", type text}}),
    Sorted = Table.Sort(OutputTypes, {{"ticket_id", Order.Ascending}, {"tag_position", Order.Ascending}})
in
    Sorted

Avoid accidental multiplication of unrelated fields

When one row expands into several tag rows, other parent columns are repeated. If you later include ticket_cost of 100 on T1, all three T1 rows could show 100. Summing that child table would report 300 for the same parent cost. Keep parent-level measures in a separate ticket table or define an explicit allocation policy before summing them at tag level.

Similarly, splitting two independent list columns one after the other can create combinations. A ticket with two tags and three assignees may produce six rows if both lists are expanded independently. Do not assume the first tag belongs with the first assignee unless the source specification defines positional pairing. This guide expands one list only and does not infer relationships between separate lists.

Know when this separator rule is too simple

Text.Split treats every semicolon as a boundary. If a tag itself can contain a semicolon, the source needs an escaping or quoting specification and an appropriate parser. Do not apply this query to a full CSV record as a replacement for CSV parsing. Outer CSV quotation rules and an inner list field require separate handling.

For the worked sample, verify six rows, three represented ticket IDs, and the exact positions listed in ticket-tags-expected.csv. A refresh test of T5 with red;;blue should add two rows at positions 1 and 3. T6 with a whitespace-only tags value should add none. Keep an audit count of omitted parents if downstream coverage reporting depends on every source ticket being represented.

Check the native result before reusing the query

On October 5, 2026, the exact downloadable M query was executed through the native Power Query engine in Excel 16.0 build 5569.0. All six rows matched ticket-tags-expected.csv, including both red occurrences on T1 and the absence of T3. The result represented three tickets and five distinct ticket-tag pairs.

Additional native cases checked red;;blue at positions 1 and 3, null, empty text and whitespace-only lists, and repeated parent IDs. Empty parents contributed no child record; repeated IDs retained separate occurrences. The query sorts by ticket_id and position, so it does not preserve original parent-row ordering when IDs are repeated. Add a source-row identifier and sort by that identifier if source-row order matters. Menu clicks and all Excel versions were not exercised.