Often you may want to change the order of columns in two different scenarios in Power BI:
Scenario 1: Change Order of Columns in Table View
Scenario 2: Change Order of Columns in a Table in Report View
The following examples show how to change the order of columns in both scenarios.
Example 1: Change Order of Columns in Table View
Suppose we have the following table in Power BI named my_data that contains information about various basketball players:
Suppose that we would like to change the columns to be in the following order:
- Team
- Rebounds
- Assists
- Points
The easiest way to do this is by using the SELECTCOLUMNS function in DAX to create a new table in which the columns are in the exact order that you specify.
To do so, click the Table tools tab and then click the New table icon:
Then type the following formula into the formula bar:
New Table = SELECTCOLUMNS ( my_data, "Team", my_data[Team], "Rebounds", my_data[Rebounds], "Assists", my_data[Assists], "Points", my_data[Points] )
This will create a new table named New Table in which the columns are in the exact order that we specified:
Note: You can find the complete documentation for the SELECTCOLUMNS function in DAX here.
Example 2: Change Order of Columns in a Table in Report View
Suppose we insert the following table in Report View that summarizes the points, assists and rebounds for each unique team:
Suppose that we would like to change the columns in the table to be in the following order:
- Team
- Sum of Rebounds
- Sum of Assists
- Sum of Points
The easiest way to do so is by clicking and dragging the column names up or down within the Columns pane of the Visualizations tab to the exact order that you’d like:
Notice that the columns in the table are now in the exact order that we specified.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI:
How to Change Legend Order in Power BI
How to Add Index Column to Table in Power BI
How to Change Column Width in Table in Power BI
The approach listed for changing the sequence of columns in the table view will have a major effect on the size of the Power BI, as you’re simply duplicating every table.
Isn’t there a cleaner way of achieving this for real world scenarios? That has thousands, if not hundreds of thousands of records in size?
Hi Doug…You’re correct that duplicating tables in Power BI can lead to unnecessary bloat, especially with large datasets. Duplicating tables is not an efficient approach when dealing with significant volumes of data, as it can negatively impact performance and file size. Instead, there are more efficient ways to change the sequence of columns or manipulate the table structure without duplicating the entire dataset.
### Cleaner Approaches for Reordering Columns in Power BI:
1. **Use a Custom Table View with Selected Columns:**
– **Method:** Instead of duplicating the table, create a new table or view using DAX that selects only the necessary columns in the desired order.
– **Implementation:**
“`DAX
NewTable = SELECTCOLUMNS(
OriginalTable,
“Column1”, [Column1],
“Column3”, [Column3],
“Column2”, [Column2],
…
)
“`
– **Benefits:** This approach doesn’t duplicate the entire table but creates a view with only the selected columns in the specified order, which is more memory-efficient.
2. **Reorder Columns in Power Query Editor:**
– **Method:** Reorder the columns directly in the Power Query Editor. This approach keeps the original table intact and simply changes the display order of the columns.
– **Implementation:**
1. Go to the Power Query Editor (`Transform Data`).
2. Drag and drop the columns to the desired order.
3. Apply the changes, and Power BI will maintain this order in the model view and visualizations.
– **Benefits:** This approach is non-destructive and efficient, as it doesn’t require creating additional tables.
3. **Use Data Model Relationships:**
– **Method:** If you need to reorder or manage columns across related tables, consider managing relationships effectively between smaller dimension tables and the main fact table.
– **Implementation:**
– Create dimension tables that hold specific columns.
– Establish relationships with the fact table.
– In your visualizations, select the relevant columns from the related tables.
– **Benefits:** This reduces redundancy and ensures a cleaner, more normalized data model.
4. **Calculated Columns or Measures:**
– **Method:** Create calculated columns or measures as needed to adjust the order or create specific views.
– **Implementation:**
– Instead of duplicating a table, add calculated columns to bring in data as required and adjust their order directly in the visuals.
– **Benefits:** This approach keeps the data model lean and allows you to dynamically adjust the order of information presented.
### Summary:
– **Efficiency:** The key to handling large datasets efficiently in Power BI is to avoid unnecessary duplication and instead leverage the platform’s built-in capabilities like custom views, Power Query, and relationships.
– **Real-World Scalability:** By reordering columns in Power Query or using calculated columns and relationships, you maintain scalability and performance even with large datasets.