Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 56 ยท Power Query

Shaping Columns and Rows

Split and merge columns, extract text, add conditional and custom columns, and keep or remove rows in Power Query, with M code and pandas stand-ins.

After cleaning, the next job is shape: turning a column such as "Ada Lovelace" into two columns, adding a column that says whether an order is "Large" or "Small", or keeping only the rows your report needs. These steps live in the Transform and Add Column tabs of the Power Query Editor.

A useful rule: Transform tab commands change the selected column in place, while Add Column tab commands create a new column and leave the original alone. When in doubt, add a column; you can always remove the old one later. For the editor basics, see The Power Query Editor.

Shaping toolbox

Split One column into many.
Merge Many columns into one.
Extract Pull out part of a text.
Add column Conditional or custom.
1 / 2

Split columns

Split Column (Transform or Home tab) offers two main modes.

  • By Delimiter: split at a character such as a comma, space or dash. You choose the leftmost delimiter, the rightmost or each occurrence, and whether the result is columns or rows.
  • By Number of Characters or By Positions: for fixed-width codes.
M
#"Split Column by Delimiter" = Table.SplitColumn(
    Source, "FullName",
    Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
    {"FullName.1", "FullName.2"}
)

After splitting, rename the new columns (the default FullName.1 is not meaningful) and check their types, because Power Query may guess. If some names have three parts ("Mary Ann Smith"), splitting at the leftmost delimiter gives Mary and Ann Smith, which may or may not be what you want.

Merge columns

Merge Columns (select two or more columns, then Transform > Merge Columns) joins them with a separator you choose. The result goes into a new column whose name you provide.

M
#"Merged Columns" = Table.CombineColumns(
    Source, {"FirstName", "LastName"},
    Combiner.CombineTextByDelimiter(" ", QuoteStyle.None), "FullName"
)

Merge works on text. To combine a number with text, first change the number's type to text, or use a custom column with Text.From.

Extract text

The Extract menu is a set of one-click text slicers: Length, First Characters, Last Characters, Range, Text Before Delimiter, Text After Delimiter and Text Between Delimiters. They are the quick way to take the year out of "INV-2025-0042" or the domain from an email address.

M
#"Extracted Text Between Delimiters" = Table.TransformColumns(
    Source, {{"InvoiceNo", each Text.BetweenDelimiters(_, "-", "-"), type text}}
)

For patterns that are hard to describe, Column From Examples (Add Column tab) lets you type the result you want in a few rows, and Power Query infers an expression. Always read the formula it generates and test it on odd rows; it is a helpful guess, not a guarantee.

Conditional columns

Conditional Column (Add Column) builds an if/then/else without writing code. You pick a column, an operator, a value and the output, and add more rules as needed.

M
#"Added Conditional Column" = Table.AddColumn(
    #"Changed Type", "Size",
    each if [Quantity] >= 10 then "Large" else if [Quantity] >= 5 then "Medium" else "Small",
    type text
)

Rules are checked from top to bottom and the first match wins, so put the most specific rule first. Do not forget the final else; without one, unmatched rows get null.

Custom columns

When the dialogs are not enough, choose Custom Column and write an M expression. It runs once for each row, and [ColumnName] reads a value from the current row.

M
#"Added Custom" = Table.AddColumn(
    #"Added Conditional Column", "Revenue", each [Quantity] * [UnitPrice], type number
)

Always supply the data type (type number here). Otherwise the column is typed any, and later steps and the model treat it as untyped. More on M expressions in M Language Basics.

Keep and remove rows

The Keep Rows and Remove Rows menus on the Home tab work by position:

  • Keep or remove Top Rows / Bottom Rows (the first or last N rows).
  • Keep Range of Rows (from a position, for a count).
  • Remove Alternate Rows or Blank Rows or Errors.

Filtering by value (the drop-down arrow on a column header) is different: it keeps rows that match a rule.

M
#"Kept Top Rows" = Table.FirstN(Source, 100),
#"Filtered Rows" = Table.SelectRows(#"Kept Top Rows", each [Region] = "North" and [Quantity] > 0)

Order matters. "Keep top 100 rows" before sorting returns an arbitrary 100; after sorting by revenue descending it returns the biggest 100.

Many filters can be translated to SQL and run inside the database, which is much faster for big tables. Filtering early (and removing unneeded columns early) is a core performance habit; see Query Folding, Incremental Refresh and Query Performance.

The same shaping in pandas

This stand-in runs every idea above: split, merge, extract, conditional column, custom column and a filter. The Power Query step each line corresponds to is in the comment.

Python Split, merge, extract and add columns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Notice that the conditional rules in size are checked in order, exactly like the Conditional Column dialog.

Rows by position or by value

The next stand-in contrasts keeping rows by position with filtering by value, and shows why sorting first matters.

Python Keep top N versus filter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Keep in mind that this is a stand-in. Power Query's Table.FirstN and the Keep Top Rows command correspond to head, and sorting is a separate recorded step with its own cost.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between a Transform tab command and an Add Column tab command?

    Transform replaces the selected column's values, while Add Column keeps the original and adds a new column next to it.
  2. 2. In a Conditional Column with several rules, which rule applies when two rules would both match a row?

    Rules are evaluated top to bottom and the first match wins, so list specific rules before general ones.
  3. 3. Why should a Custom Column step include a type, such as type number?

    Without the type argument the column has type any. It still works, but you lose type information for later steps and the data model.
  4. 4. You want the 100 biggest orders by revenue. What is the correct order of steps?

    Keep Top Rows works by position. Sorting first puts the biggest orders on top, so the first 100 rows are the 100 biggest.

Summary

  • Transform changes a column in place; Add Column creates a new one.
  • Split by delimiter or position, merge with a separator, and extract text with the Extract menu.
  • Conditional columns check rules top to bottom; first match wins. Add a final else.
  • Custom columns use M expressions per row; give them a type.
  • Keep/Remove Rows work by position, filters work by value, and step order changes the result.

Next is changing the table's whole orientation in Unpivot, Pivot and Transpose.