Downloads

M tables: rows, columns, joins and groups

Select, add and change columns, merge, group, pivot and unpivot, plus text, date and number functions.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Select, add and change columns, and combine tables with merges and appends.
  • Group, pivot and unpivot data in M.
  • Clean the headers and layout of a pasted report.
  • Use text, date and number functions to fix common data problems.

Part 2 of 4. Examples use classicmodels; see M basics for how to connect and read a query.

Tables

Most of your M work is table functions. Here is the set that covers daily use. Each takes a table and returns a new table.

Choose rows and columns

Table.SelectRows(T, each [amount] > 1000 and [country] = "USA")
Table.SelectColumns(T, {"orderNumber", "orderDate"})
Table.RemoveColumns(T, {"comments"})
Table.RenameColumns(T, {{"orderNumber", "Order #"}})
Table.ReorderColumns(T, {"orderNumber", "status"})
Table.Distinct(T, {"customerNumber"})
Table.Sort(T, {{"orderDate", Order.Descending}})
Table.FirstN(T, 10)          Table.Skip(T, 2)          Table.RemoveFirstN(T, 2)

Add and change columns

Table.AddColumn(T, "Total", each [quantityOrdered] * [priceEach], type number)
Table.TransformColumns(T, {{"status", Text.Upper, type text}})
Table.ReplaceValue(T, null, 0, Replacer.ReplaceValue, {"discount"})
Table.FillDown(T, {"region"})

Table.AddColumn takes a name, a function for each row and an optional type. Always give the type as the fourth argument. Without it the new column is any and loads as text.

Combine tables

// Stack: same columns, more rows
Table.Combine({Orders2025, Orders2026})

// Join (a "merge" in the UI): then expand the columns you want
Merged = Table.NestedJoin(Orders, {"customerNumber"}, Customers, {"customerNumber"}, "Customer", JoinKind.LeftOuter),
Expanded = Table.ExpandTableColumn(Merged, "Customer", {"customerName", "country"}, {"customerName", "country"})

The join kinds are JoinKind.Inner, LeftOuter, RightOuter, FullOuter, LeftAnti and RightAnti. They match the SQL joins in the Schema explorer Join Lab. Merge on columns with the same data type, or no rows will match.

Group (the GROUP BY of Power Query)

Table.Group(
    Lines,
    {"productCode"},
    {
        {"Units", each List.Sum([quantityOrdered]), type number},
        {"Lines", each Table.RowCount(_), Int64.Type}
    }
)

Inside a group, _ is the sub-table for that group, so Table.RowCount(_) counts its rows. Use Table.Group instead of looping.

Pivot and unpivot

// Columns become rows: keep the ID columns, unpivot the rest
Table.UnpivotOtherColumns(Wide, {"region"}, "Month", "Sales")

// Rows become columns
Table.Pivot(Long, List.Distinct(Long[Month]), "Month", "Sales", List.Sum)

Unpivoting is how you turn a spreadsheet-style report (months across the top) into a proper table. Practice it on lab_piv_sales_wide in pqlab.

Wide: a column per month Region Jan Feb Mar North 120 135 150 South 90 95 110 unpivot the months Long: a row per region and month Region Month Sales North Jan 120 North Feb 135 North Mar 150 South Jan 90 South Feb 95 South Mar 110
Unpivot turns columns into rows. Keep the region column, and turn the month columns into a Month and a Sales column.

Headers and layout

Table.PromoteHeaders(T, [PromoteAllScalars = true])
Table.DemoteHeaders(T)
Table.Transpose(T)

Text, dates and numbers

Text.Trim([name])                     Text.Clean([name])
Text.Upper([x])  Text.Lower([x])      Text.Proper([x])
Text.Start([x], 3)  Text.End([x], 4)  Text.Middle([x], 2, 5)
Text.Contains([x], "ship")            Text.StartsWith([x], "A")
Text.Replace([x], "-", "")            Text.Length([x])
Text.Split([x], ",")                  Text.BeforeDelimiter([x], "@")
Text.PadStart(Text.From([n]), 6, "0") // 42 -> "000042"
Date.From([stamp])                    // datetime to date
Date.Year([d])  Date.Month([d])  Date.Day([d])
Date.MonthName([d])  Date.DayOfWeekName([d])
Date.StartOfMonth([d])  Date.EndOfMonth([d])  Date.StartOfWeek([d])
Date.AddDays([d], 30)  Date.AddMonths([d], -1)
Date.ToText([d], "yyyy-MM")           // text with a format
Duration.Days([shipped] - [ordered])  // days between two dates
Date.From(DateTime.LocalNow())        // today
Watch out

Subtracting two dates gives a duration, not a number. Wrap it: Duration.Days(...).

Number.Round([x], 2)    Number.RoundUp([x])    Number.RoundDown([x])
Number.From("12.5")     Number.FromText("12.5")  Text.From(12.5)
Number.Mod(7, 3)        Number.IntegerDivide(7, 3)

Previous: M basics: queries, values, lists and records. Next: M functions and patterns. Series: basics, tables, functions and patterns, performance.

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.