Power BI And Column Order In M Queries And Dataset Tables

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:

image

Here’s the M code for this query:

#table(
    type table [Column1=number, Column2=number, Column3=number],
    {{1,2,3}}
)

The output you see in the Data pane in the main Power BI window is, unsurprisingly this:

image

However, if you subsequently edit the query above so the same columns are present but in a different order, like so:

image

#table(
    type table [Column2=number, Column3=number, Column1=number],
    {{2,3,1}}
)

…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:

image

When you do this, you get the following warning:

image

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

image

You will have to manually refresh the query in the main Power BI Desktop screen to see the data in the table:

image

5 responses

  1. 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

  2. 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”

  3. Pingback: Last Week Reading (2018-12-23) | SQLPlayer

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: