Power QueryExercises

Split column recipes: names, codes, addresses and lists

Recipes · By Tahir Nas · Examples run in our simulator · Published · Updated

Jump to the practice tasks

Split at the last space (surname last)

Order A1 has tags red;large and A2 has blue. Split into columns gives Tags.1 and Tags.2, with null where a row has fewer parts. Split into rows gives one row per tag: A1 red, A1 large, A2 blue.
Splitting into columns guesses a maximum number of parts; splitting into rows doesn't.

"Mary Ann Lee" split at every space gives three pieces. Splitting at the last space keeps the given names together:

Table.SplitColumn(
    #table({"Name"}, {{"Mary Ann Lee"}, {"Ben Okafor"}, {"Cleo"}}),
    "Name",
    Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.None, true),
    {"Given", "Family"}
)

Result

GivenFamily
Mary AnnLee
BenOkafor
Cleonull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

The third argument true starts from the end. A single name leaves Family null; decide whether that's a given or a family name.

Several possible delimiters

Table.SplitColumn(
    #table({"Tags"}, {{"red,large"}, {"blue;small"}, {"green"}}),
    "Tags",
    Splitter.SplitTextByAnyDelimiter({",", ";"}),
    {"Colour", "Size"}
)

Result

ColourSize
redlarge
bluesmall
greennull

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Fixed positions (codes with a known layout)

A product code like GB2026KT0042 has country (2), year (4), family (2) and number (4). When the layout is guaranteed, split by positions:

Table.SplitColumn(
    #table({"Code"}, {{"GB2026KT0042"}, {"DE2025RL0107"}}),
    "Code",
    Splitter.SplitTextByPositions({0, 2, 6, 8}),
    {"Country", "Year", "Family", "Number"}
)

Result

CountryYearFamilyNumber
GB2026KT0042
DE2025RL0107

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Positions are start offsets from 0. Use this only when every code really has this layout; validate lengths first.

Split into rows

For a list of items in one cell, split into a list and expand to rows. This keeps one row per item and repeats the other columns:

let
    Orders = #table({"OrderID", "SKUs"}, {{"A-1", "K-01, R-02"}, {"A-2", "L-03"}}),
    Lists = Table.TransformColumns(Orders, {{"SKUs", each List.Transform(Text.Split(_, ","), Text.Trim)}}),
    Rows = Table.ExpandListColumn(Lists, "SKUs")
in
    Rows

Result

OrderIDSKUs
A-1K-01
A-1R-02
A-2L-03

Output produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M.

Splitting into rows changes the grain of the table: one order became two rows. Keep that table separate from order-level amounts, or totals will double. The same caution applies to every list expansion.

When the number of pieces varies a lot (tags, recipients, product codes), splitting into rows is almost always better than splitting into columns. Columns force you to guess the maximum number of pieces, and next month's data will exceed it.

Before you split

  • Trim first, or leading spaces create empty first pieces.
  • Decide how many output columns you need; extra pieces are dropped silently.
  • For addresses, prefer extracting known parts (postcode at the end) over splitting everything; address formats vary too much.

Practise it in the simulator

Each task opens the exact exercise in the browser workspace and is checked against fresh data, not just the example. These tasks are part of Pro.

Lessons

More resources

Function reference

Sources

Microsoft's official documentation for the features and functions used on this page.

Power Query Exercises is an independent educational platform and is not affiliated with, endorsed by, sponsored by, or approved by Microsoft Corporation. All third-party trademarks belong to their respective owners.