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.
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
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.