The order of the columns in a table in a Power BI dataset doesn’t matter all that much, especially because the Fields pane in Power BI Desktop ignores the original column order and lists the columns in a table in alphabetical order. However there are a few situations where it is important, for example when you are using the DAX Union() function in a calculated table: as the documentation states, when you use Union() “Columns are combined by position in their respective tables”. You might also find it irritating if the columns you see in the Data or Relationships panes in the main Power BI Desktop window make it hard to browse the data or create relationships. You can control the order of columns in a table in the Power Query Editor but it’s not completely straightforward to get any changes to column order you make in your queries to be applied to the order of the columns in a table after it has already been loaded into a dataset.
Let’s say you have an M query that returns a table with three columns, Column1, Column2 and Column3, that looks like this:
Here’s the M code for this query:
[sourcecode language='text' padlinenumbers='true']
#table(
type table [Column1=number, Column2=number, Column3=number],
{{1,2,3}}
)
[/sourcecode]
The output you see in the Data pane in the main Power BI window is, unsurprisingly this:
However, if you subsequently edit the query above so the same columns are present but in a different order, like so:
[sourcecode language='text' ]
#table(
type table [Column2=number, Column3=number, Column1=number],
{{2,3,1}}
)
[/sourcecode]
…you will find that it does not affect the order of the columns shown in the Data pane in Power BI Desktop, which are fixed in the order they were when they were first created.
To get the column order change to be applied to the table in the dataset, you need to go back into the Query Editor, right-click on the Query and deselect the Enable Load option:
When you do this, you get the following warning:
If you subsequently click Continue and then Close & Apply, the dire warnings will come true: you will not only break any visuals that use this table but any measures or calculated columns associated with this table will be lost. If you instead click Continue and then click the Enable Load property on the query so loading is enabled again, you will find that when you then click Close & Apply the visuals will still work, the columns have been reordered, but the measures and calculated columns will still be lost. You’ll also see something that I think is a bug: there will be no data in the table in the dataset
You will have to manually refresh the query in the main Power BI Desktop screen to see the data in the table:
Thanks for taking the time to blog about this. I too believe this behaviour is a glitch. Very irritating to expect a trivial outcome but getting blanks and then spend some time figuring out that you need to manually refresh tables and watch out for the order of the columns to get the union() function to work properly. The append function in M works like it should though
Hi Chris,
thank you for the post.
We have encountered a similar problem a while ago. A coworker wanted to perform some quick and dirty analysis of his survey results.
To keep it short, the column order was relevant, about 200 columns in total and some where just named “Other” and referred to another column next to each of them.
In Power BI Desktop, they would all show up in alphabetic order, as a block of “Other” to “Other_23” etc. So, we have prefixed all columns with their position ( 001_NameOfFirstColumn, 002_xyz , …).
Again, this is far from being a clean model but he wanted to quickly visualize some results with PBI.
Anyhow, here is a snippet of the code to prefix columns in Power Query. If somebody runs into the same problem:
let
PositionPrefix = (curColumnName as text, Columns as list) =>
let
Idx = Number.ToText (List.PositionOf(Columns, curColumnName) + 1 , “000”),
ColumnNameNew = Idx & “_” & curColumnName
in
ColumnNameNew,
// ——————————————-
Source = Excel.Workbook(File.Contents(“H:\sample.xlsx”), null, true),
Sheet1_Sheet = Source{[Item=”Sheet1″,Kind=”Sheet”]}[Data],
#”Promoted Headers” = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#”Renamed Columns” = Table.RenameColumns(#”Promoted Headers”, List.Transform(Table.ColumnNames(#”Promoted Headers”), each {_ , PositionPrefix(_ , Table.ColumnNames(#”Promoted Headers”)) } ))
in
#”Renamed Columns”
I have try to solve it after read this article.
by referring this article.
here’s my post on linkedin
https://www.linkedin.com/feed/update/urn:li:activity:6481042440756260864/
Hi Chris,
Nearly a year after your blog post but just what I needed to know.
Thanks!
Thank you so much.
The solution works so fine.
Thank you. I was using a Union and the reordering was causing problems. Your solution solved it.
This still didn’t work for me.
I ran into this issue recently and found a more simple solution for my setup. I had a 2020 sales table (static data) unioned with a 2021 sales table (refreshed daily). When the year rolled to 2022, I decided to add a third table vs modifying the existing tables SQL queries, as I wasn’t sure if I wanted to keep 2020 sales data due to file size. The SQL query was the same for all (adjusted filters), however in 2020 and 2021, columns had been added after the fact, so they were not aligning with a fresh table (2022) pulled with the same code.
In order to fix this, I went to Edit Query on my new table, deleted the columns that were not aligned, and Close & Apply. After the data model finished refreshing, I went back into the Edit Query and deleted the Delete Column step, and Close & Apply. This forced PBI to place those columns at the end of the existing table, and realigned my 2022 data so that the union on all three worked.
Hope this helps someone!
Hi Chris. Could you also please check the problem in the link https://community.powerbi.com/t5/Desktop/Sort-order-in-power-query-and-data-view-DAX-for-grouping-ids/m-p/2902815
Still useful in 2024, thanks!