Order is a crucial force in life, especially if you are a data analyst. But as we will see today, the misuse of order might cause an unexpected chaos. In today’s post, we will discuss the chaotic nature of column ordering in the Query Editor of Power BI and Power Query / Get & Transform in Excel, and we will learn how to avoid the fourth pitfall of column ordering.
You can follow the Pitfalls series here, and subscribe to DataChant below to get the file sample.
Background
In this article, we will discuss the reordering of columns. We’ll start with the rationale of column reordering (in this section), and will then describe the mistake we do when we are using it (The next section).
Note: To avoid confusion, we will not discuss the sorting of rows by columns, but the rearranging of columns’ positions in the queries and tables.
Reordering of columns is not supported as part of the Data Modeling (PowerPivot), but can be achieved in the data preparation phase in the Query Editor of Power BI and Excel Power Query / Get & Transform.
To reorder columns, you can simply select the columns and drag and drop them to their new position.
(This is how the dragging experience looks like):
Why do we need column reordering?
Column reordering in the Query Editor is quite useful. Here is a summary of the scenarios that support column reordering, and those that don’t support it.
In Excel, you can load the data into a table in the worksheet and control the order of the columns with the Query Editor. You can also control the order of the fields in the PivotTable / PivotChart field list.
Unfortunately, in Power BI, you will not maintain the columns’ order in the Fields list. Power BI automatically sorts the fields by alphabetic order.
The only view in Power BI Desktop where you can see the correct column order is the Data view.
Where else can we take advantage of column reordering? Inside the Query Editor to help us to author further steps. For example, when you want to merge multiple columns, or remove duplicates, you may reorder the relevant columns, to ease your multi-column selection.
You can also reorder a custom column for debugging purposes, and see how the calculated values in the new column are affected by values in other columns.
The screenshot below illustrates the need to reorder columns to debug custom columns. In the screenshot below, I created a new column which adds 1000 to the value in Column2.
Unfortunately, the new custom column, is always added at the end of the table, which makes it quite challenging to move the new column to the left, if we have dozens of columns. But we always move the custom column to a better location, to help us testing our calculation.
Note: Vote here for this idea: When we add Custom Column, allow us to choose its position.
Trick: If you have hundreds of columns in the preview of the Query Editor, and you want to move the last column to the left side, use the Move > To Beginning in Transform tab.
Pitfall #4
So now when we know all the great advantages of column reordering in the Query Editor, let’s demonstrate how we always take a big risk when we use it as-is.
Let’s say, we want to reorder the last two columns as the second and third columns.
Following the reordering of the last two columns, Power Query will auto-generate the formula which is needed for the reordering step. If you have the formula bar open (Which you do, if you have avoided Pitfall #1 here), you can now see that formula refers to ALL of the columns names, and this is the pitfall!!!
Imagine that our external data source always includes the last four years. When we created the query, we had columns 2013, 2014, 2015 and 2016, but in 2017, we will have 2014, 2015, 2016 and 2017. Column 2013 will not exist anymore.
And the result – The auto-generated code will fail to refresh.
The example we use here is relatively naive. But let’s continue to use it to illustrate the challenge at hand. So, how can we avoid the pitfall, and create a robust query that will not fail to refresh when the data changes.
We can always avoid the reordering altogether. In our naive case, we can simply select the columns Region, RegionManager and HeadCount (Using the CTRL key, as the columns are not adjacent), and apply Unpivot Other Columns to have a table with the Year and Amount.
Tip for new-comers: If you have years or dates as column names in your tables, you are missing a great potential of insights. Unpivot your table (Here is my unpivot series).
How can we fix the formula below to prevent refresh errors?
= Table.ReorderColumns(Source,{"Region", "RegionManager", "HeadCount", "2013", "2014", "2015", "2016"})
We can add the optional attributes MissingField.Ignore, or MissingField.UseNull which will avoid refresh errors (here is the official Table.ReorderColumns page, which doesn’t mention MissingField.Ignore).
Here is the fixed formula that will avoid refresh errors and will ignore column 2013:
= Table.ReorderColumns(Source,{"Region", "RegionManager", "HeadCount", "2013", "2014", "2015", "2016"}, MissingField.Ignore)
Hurray, the refresh didn’t fail. But the reordering ignores column 2017.
On many scenarios, you would prefer to keep having the missing column (especially if you need it in later transformation steps). To keep the missing columns you can use MissingField.UseNull, as shown in this formula:
= Table.ReorderColumns(Source,{"Region", "RegionManager", "HeadCount", "2013", "2014", "2015", "2016"}, MissingField.UseNull)
With MissingField.UseNull we were able to keep column 2013 with null values. Still, the column reordering wasn’t achieved properly (Column 2017 is before 2014).
Could we somehow reorder only a subset of the columns, and keep the remaining columns intact?
Ideally, we would use the following formula to move the specified columns to the start of the table:
= Table.ReorderColumns(Source,{"Region", "RegionManager", "HeadCount"})
But the formula above has no effect on the column order. It remained the same.
Here is another failed attempt to specify a subset of the columns:
You can see that HeadCount remained at the end, with 2014 as the new last column. The fact that we need to refer to column 2014 is not reasonable, given the potential refresh failure.
Here is another failed attempt, which is mainly bad, as we refer to two columns.
So what is the rationale of using Table.ReorderColumns with a subset of the columns? To shuffle and switch between columns. So for a table with columns Col1, Col2, Col3, Col4, Col5, Table.ReorderColumns(MyTable, {“Col2“, “Col1“}) will switch between Col1 and Col2, and Table.ReorderColumns(MyTable, {“Col5“, “Col3“, “Col2“}) will reshuffle the three columns, but will leave Col1 and Col4 untouched.
While this method can be handy to shuffle between few selected columns, it is useless for moving few columns over a long subset of columns, as we need to refer to all the column names in the code.
So here is the ideal (yet advanced) solution that requires the use of a
The Robust Query
Finally, we have reached our best solution, that will allow us gain the following objectives:
- Reorder the specificed crucial columns
- Maintain the original order of the unspecified columns
- Position the newly ordered subset in ANY specified position
- Don’t refer to any unnecessary columns
By fulfilling the objectives above, the fourth pitfall is avoided.
And here is the solution:
Create a blank query with the code below, and rename the query to FnReorderColumnsSubset.
You can copy the code from here, or download the PBIX sample if you are a DataChant subscriber (Subscribe form below).
(tbl as table, reorderedColumns as list, offset as number) as table =>
Table.ReorderColumns
(
tbl,
List.InsertRange
(
List.Difference
(
Table.ColumnNames(tbl),
reorderedColumns
),
offset,
reorderedColumns
)
)
The function receives the table, a subset list of the ordered column names, and a zero-based offset. The main transformation is Table.ReorderColumns, with a dynamic list of columns that is created using a combo of List.InsertRange and List.Difference.
Here is how you can use it to solve our challenge:
To arrange the first N columns in your table, use this formula, with an offset of zero. In our case we will use Region, RegionManager and HeadCount with offset of zero.
= FnReorderColumnsSubset( Source, { "Region", "RegionManager", "HeadCount" }, 0 )
To be more precise, in our example, we could also just arrange RegionManager and HeadCount in after the first column (offset 1):
= FnReorderColumnsBySubset( Source, {"RegionManager", "HeadCount"}, 1 )
You can use different offsets to position the specificed ordered columns anywhare in the table. In the example below, I positioned Region, RegionManager and HeadCount in offset 2 (Third column).
Subscribe to DataChant to get Pitfall 4 Sample.PBIX file with the example, and query.
Coming Up: Pitfall #5.
[rad_rapidology_inline optin_id=optin_5]
Another great help to build robust queries! Thanks for sharing!
Check the second to last screenshot: You call a function called fnReorderTABLEBySubset. Shouldn’t it be fnReorderCOLUMNSBySubset?
Hi Dominik,
Thank you for the feedback. You are right. It should be fnReorderColumnsSBySubset. I fixed the it above.
Hope you find the next six pitfalls as helpful 🙂
Pingback: Removing/Selecting Columns - Pitfall #5 in #PowerBI and #PowerQuery - DataChant
Pingback: Column Renaming and List.Zip To The Rescue - Pitfall #6 in #PowerBI #PowerQuery - DataChant
No PBIX file included in the zip file?! Please check it out. Thanks.
Hi Julian,
The file is a PBIX file, not a ZIP one. Try to open it with Power BI Desktop.
Yes, it’s truly a PBIX file with .zip file extension. I could open it with Power BI Desktop as you said.
Thank You, Jil. I find your series of articles very useful and interesting.
Thank you Drigloff. I am glad you find it useful.
Nicely done!
Excellent solution! Thanks
This is awesome, thank you! Great to use with when the number of columns isn’t fixed across the different files (I’m doing a Combine Files From Folder)!
Exactly what I needed! THANK YOU!!!
Thank you Kade. I am glad you found it useful!
thank you very much for this very helpful function!
Hi Gil, Im very new to BI, and trying to write the below query to re-order my columns in an exsiting table.
below is my query. However, i am not getting any data table with this. What am i doing wrong?
= Table.ReorderColumns(#”RDP – Accrual”,List.InsertRange{“source.name”,”Business Unit”,”Ledger”,”Account Code”,”Description”,”Accounting Period”,”Base Amount”,”Currency Code”,”Transaction Date”,”Journal No.”,”Journal Line”,”Journal Type”,”Journal Source”,”Transaction Reference”,”Description”,”Contract Analysis Code”,”Discipline Analysis Code”,”FOS Region Analysis Code”,”Store Analysis Code”,”Name”,”GST Analysis Code”,”Employee Analysis Code”,”Name”,”Supplier/Customer Analysis Code”,”Name”,”Job Number Analysis Code”,”ROM Region Analysis Code”,”GL Code Analysis Code”,”Hard Posting Date”,”Fault Service”,”Coles Line No”,”Asset and Asset Type”,”Original PO No”,”COMS Event ID”,”iPOS Document No”,”iPOS PO Number”,”User Name”,”Lookup Code”,”Lookup Code”,”Name”,”A Code for Management Accounts Analysis Code”),List.Difference(Table.ColumnNames(#”RDP – Accrual”),{“source.name”,”Business Unit”,”Ledger”,”Account Code”,”Description”,”Accounting Period”,”Base Amount”,”Currency Code”,”Transaction Date”,”Journal No.”,”Journal Line”,”Journal Type”,”Journal Source”,”Transaction Reference”,”Description”,”Contract Analysis Code”,”Discipline Analysis Code”,”FOS Region Analysis Code”,”Store Analysis Code”,”Name”,”GST Analysis Code”,”Employee Analysis Code”,”Name”,”Supplier/Customer Analysis Code”,”Name”,”Job Number Analysis Code”,”ROM Region Analysis Code”,”GL Code Analysis Code”,”Hard Posting Date”,”Fault Service”,”Coles Line No”,”Asset and Asset Type”,”Original PO No”,”COMS Event ID”,”iPOS Document No”,”iPOS PO Number”,”User Name”,”Lookup Code”,”Lookup Code”,”Name”,”A Code for Management Accounts Analysis Code”}),Table.ColumnNames(#”RDP – Accrual”,{“source.name”,”Business Unit”,”Ledger”,”Account Code”,”Description”,”Accounting Period”,”Base Amount”,”Currency Code”,”Transaction Date”,”Journal No.”,”Journal Line”,”Journal Type”,”Journal Source”,”Transaction Reference”,”Description”,”Contract Analysis Code”,”Discipline Analysis Code”,”FOS Region Analysis Code”,”Store Analysis Code”,”Name”,”GST Analysis Code”,”Employee Analysis Code”,”Name”,”Supplier/Customer Analysis Code”,”Name”,”Job Number Analysis Code”,”ROM Region Analysis Code”,”GL Code Analysis Code”,”Hard Posting Date”,”Fault Service”,”Coles Line No”,”Asset and Asset Type”,”Original PO No”,”COMS Event ID”,”iPOS Document No”,”iPOS PO Number”,”User Name”,”Lookup Code”,”Lookup Code”,”Name”,”A Code for Management Accounts Analysis Code”},0,))
Hi Tyleen,
Thank you for sharing. I recommend that you first share this challenge in the Power BI forum. You may get a faster response 🙂
Thanks a lot Gil! This helped me a lot.
Gil, your pitfall series helped me to save my day. But I am having trouble with sorting query steps. I do have a dataset which has 122 columns although I need only 25 of them. Is following the steps that I written below a good idea or should I make a change in the order?
1. Table.SelectColumns (25 columns)
2. Order Columns
3. Rename Columns
and continue to other steps. I am having trouble with those steps. I would be appreciated if you can give an advice!
Hi Taylor
The order looks correct. Do you have any issues with these steps? It wasn’t clear from the comment.
Hi, Gil! Reading your book about Power Query, and the question arose:
Tell me, please, can i use the FnReorderColumnsBySubset function if i need to move several non-consecutive columns to other places in the table? For example, there were columns: 1 2 3 4 5 6. I need to do: 1 4 2 3 6 5. Сan I implement this in one step – using the function FnReorderColumnsBySubset?
Yes. You can. Feel free to reach out if you need help.
How does one subscribe to datachant.com in order to download any resource files? I can’t find a new subscriber form anywhere. Thank you.
Hi Kimo
The blog is in a low maintenance mode right now and subscription is not active. All files can be found in https://datachant.com/samples/
Thanks so much Gil this is fantastic. Those verbose ReorderColumns steps have plagued me for a long time.
I extended your function call by automatically determining the offset…
= FnReorderColumnsSubset(#”Renamed HFM Code”, {“BU Name”, “HFM Code”}, Table.Schema(#”Renamed HFM Code”){[Name=”BU Name”]}[Position])
This allows me to bring the columns “BU Name” and “HFM Code” together, at whatever position the “BU Name” column is sitting in the previous step.
Shout if you see any drawbacks with this approach! Thank you again.