BUG: Reorder.Columns?

[Alex_Ooi]

Hi,

This is the second time I am encountering a weird problem when I use Reorder.Columns( ) function after I have created some calculated columns (in DAX).

What I do is this:

1. Load my dataset

2. Created some calculated columns (in DAX)

3. Realised I missed some columns that I should actually code in M (since it's related to ETL)

4. Created some computed columns, in M, using Table.AddColumn ( )
5. Reordered my computed columns using Reorder.Columns( )

The funny thing is the dataset looks exactly like what I reordered them to be in the Power Query view, but once I hit "Close & Apply" and go back to viewing the dataset in Power BI interface, the columns were still at the rightmost side.

What I did in the past when my dataset and workings in DAX aren't that many (yet) was to copy + paste my M code into a blank query and it worked fine. I don't think doing this now is something I'd want to do because it is really a big headache now when everything has expanded.

Any advise? Looking forward to hearing from you experts. Thanks!

Regards,

Alex

[edhans · Accepted solution]

Once the data is loaded to the data model, changing column order in Power Query does nothing. The only fix is to unload, apply, and reload the table. Of course, that will destroy any calculated columns, measures, visuals, and filters you were using. I don't know if that is a bug as much as it is a design issue.

In designing reports though it doesn't really matter. When choosing fields in both the Report and Model views, they are alphabetcial. You only see this issue in the Data view, which I try to stay out of anyway. I avoid calculated columns as much as possible.

[edhans · Accepted solution]

Once the data is loaded to the data model, changing column order in Power Query does nothing. The only fix is to unload, apply, and reload the table. Of course, that will destroy any calculated columns, measures, visuals, and filters you were using. I don't know if that is a bug as much as it is a design issue.

In designing reports though it doesn't really matter. When choosing fields in both the Report and Model views, they are alphabetcial. You only see this issue in the Data view, which I try to stay out of anyway. I avoid calculated columns as much as possible.

[Alex_Ooi]

Alas, there is really no other way then. Sigh...

Thanks for your advice tho.

[Jimmy801]

Hello @Alex_Ooi ,

but did you try my proposal?

Jimmy

[Alex_Ooi]

I basically duped my entire query back, as what I did in the past. Needless to say, it is hurtful to do the remodelling etc, but better now than later. Thanks tho.

[Jimmy801 · Accepted solution]

Hello @Alex_Ooi 

so you did try my solution that i proposed to

- leave your query as it is, but passing for refreshing purposes a table one new column onl (add new final step that procudes a table with one column only)

- update datamodel (now with errors, because of the missing columns)

- delete final step to pass your query

it would be interesting to know if this could solve your issue

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

[Jimmy801]

Hello @Alex_Ooi ,

I'm not 100% sure but I think once handed over to the datamodel, the order of the columns are not part of the interface, meaning that only columnNames and their content part of the update procedure. Interesting thing is that the sort order is updated in the diagram view.

What you could try to do is to copy content of your query, delete it all, fill it with an empty tabel with one column, update power pivot and then go back to your query an paste your query back in.

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy