<!-- https://learnsql.cpetoday.com/analyze/power-query-m-tables -->
# M tables: shaping rows, columns, joins and groups

Part 2 of 4. Examples use `classicmodels`; see [M basics](/analyze/power-query-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

```powerquery
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

```powerquery
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

```powerquery
// 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](/relationships) Join Lab. Merge on **columns with the same data type**, or no rows will match.

### Group (the `GROUP BY` of Power Query)

```powerquery
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

```powerquery
// 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`.

> Diagram: A wide table with a column for each month becomes a long table with one row for each region and month. Made-up numbers, for illustration.

### Headers and layout

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

## Text, dates and numbers

```powerquery
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"
```

```powerquery
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(...)`.

```powerquery
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](/analyze/power-query-m-basics).
Next: [M functions and patterns](/analyze/power-query-m-patterns).
Series: [basics](/analyze/power-query-m-basics), [tables](/analyze/power-query-m-tables), [functions and patterns](/analyze/power-query-m-patterns), [performance](/analyze/power-query-m-performance).
