Table.SelectColumns
Table function · By Tahir Nas · Examples run in our simulator · Published · Updated
Keeps the listed columns, in the listed order.
When to use it
Use it when a report needs a fixed set of columns in a fixed order. Columns the source adds later are left out, which keeps downstream tables stable. If you'd rather keep everything except a few columns, use Table.RemoveColumns instead.
Syntax
Table.SelectColumns(table as table, columns as any, optional missingField as nullable number) as tableParameters
- columns
- A column name or list of names to keep.
- missingField
- MissingField.Error (default), MissingField.Ignore or MissingField.UseNull.
Returns
A table with only the chosen columns.
Example
Table.SelectColumns(#table({"Region", "Product", "Qty"}, {{"North", "Kite", 2}, {"South", "Reel", 5}, {"North", "Reel", 1}, {"West", "Kite", 4}}), {"Product", "Qty"})Result
| Product | Qty |
|---|---|
| Kite | 2 |
| Reel | 5 |
| Reel | 1 |
| Kite | 4 |
Example: tolerate a column the source may not send
MissingField.UseNull keeps the query running and fills the missing column with null instead of failing.
Table.SelectColumns(#table({"Region", "Amount"}, {{"North", 120}}), {"Region", "Amount", "Rep"}, MissingField.UseNull)Result
| Region | Amount | Rep |
|---|---|---|
| North | 120 | null |
Both results are produced by the Power Query Exercises simulator, an independent implementation of a documented subset of M, and checked on every build.
Good to know
- Columns added to the source later are not included, which makes reports stable.
- The output order follows the list you give.
Common mistakes
- Listing a column with different capitalisation: names are case-sensitive.
- Using it on a feed that gains useful columns over time, then wondering why they never appear.
The matching command in Excel
- Excel for Windows
- Home > Choose Columns
- What you see
- A checklist of every column opens; the ticked columns are kept.
Paths are for the Power Query editor in Excel for Microsoft 365, checked against Microsoft's documentation on 8 November 2026: Choose or remove columns (Microsoft Learn).
Exercises that use Table.SelectColumns
- beginner~6 minFree
Keep order numbers' leading zeros (kite shop)
Type a CSV so order numbers stay text (0007, not 7) while quantities and prices become numbers.
- intermediate~8 minFree
Convert day-first dates with the right locale (consultancy)
Claim dates are written 05/03/2026 meaning 5 March. Convert them to real dates using the en-GB culture.
- beginner~5 minFree
Keep only the columns a report needs (kite shop)
Choose OrderID, Customer and Qty so the report stays the same shape even when the export gains new columns.
- beginner~6 minFree
Rename and reorder columns for a finance report (kite shop)
Give columns business-friendly names and put them in the order finance expects.
- intermediate~8 minFree
Find the three largest orders (kite shop)
Calculate each order's value and keep the three biggest, largest first.
- intermediate~7 minFree
Fix a query that removes a column before using it (kite shop)
The query trims columns too early, so the Revenue step can't find UnitPrice. Reorder the steps.
Learn more
Related functions
Official documentation: Table.SelectColumns on Microsoft Learn.