Table.ReorderColumns

Updated on

Table.ReorderColumns is a Power Query M function that reorders columns in a table according to a specified column order. The function returns a new table with the columns reordered, handling missing columns based on an optional parameter.

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Table.ReorderColumns(
   table as table,
   columnOrder as list,
   optional missingField as nullable number,
) as table
Argument Attribute Description
table
columnOrder
missingField optional The MissingField.Type determines the function’s reaction to operations on missing columns. When omitted, it uses MissingField.Error and generates an error for missing columns. Alternatives include MissingField.UseNull, substituting null for missing columns, and MissingField.Ignore, which ignores missing columns.

Description

Table.ReorderColumns returns a table with columns in specified order. It reorders columns in table as per columnOrder. Columns not in the list remain unaltered. Errors are thrown for non-existent columns unless missingField specifies otherwise.

Examples

Switch the order of the columns [Phone] and [Name] in the table.

// Output: Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} )
Table.ReorderColumns( 
    Table.FromRecords( {[CustomerID = 1, Phone = "123-4567", Name = "Bob"]} ),
    {"Name", "Phone"}
 )

Switch the order of the columns [Phone] and [Address] or use “MissingField.Ignore” in the table. It doesn’t change the table because column [Address] doesn’t exist.

// Output: Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} )
Table.ReorderColumns( 
    Table.FromRecords( {[CustomerID = 1, Name = "Bob", Phone = "123-4567"]} ),
    {"Phone", "Address"},
    MissingField.Ignore
 )

Other functions related to Table.ReorderColumns are:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/table-reordercolumns