Order of columns changes in Data View after Edit Query phase

[alfredas81]

Hi,

I am new to Power BI, but I can not understand why the order of columns which was in Edit Query phase(no reordering was applied to column order) changed in Data View. 

Order of table columns in Edit Query mode

And after hiting close and apply 

The problem is that same querys do not change order of columns when exiting Edit Query mode.

[v-yulgu-msft]

Hi @alfredas81,

I cannot reproduce this problem in my test. What is the source table sturcture? What operations did you make in Query Editor mode? Did you create custom columns based on original columns?

It looks like only the order of custom/conditional columns will be changed.  All these columns will be displayed at the right of original columns.

This behavior won't affect the report design, as you need to add columns into visual manually. And the column order in data model doesn't dertermine the column order in visual.

Regards,
Yuliana Gu

[alfredas81]

Hi Yuliana Gu,

Data source is join of multiple SQL statements. Actually no conditional/custom columns were added to original table in Edit Query phase:

The problem is that I use this table to join with tables which has the same structure, and because column order is changed, union function does not provide correct results in Data View phase.

[bbaranyai]

Alfredas81 and Yuliana,

I have the same problem.

I am combining data from multiple excel sheets in my query. One of the columns in the main query is created by using a lookup table (merge queries). This column appears in Query Editor in 72th position....when I close and apply it moves to 75th position. Only that column...all the other columns in Data View match their positions in Query Editor.

Thanks and regards,

Balazs

[freder1ck]

I would try setting the column positions manually ...

= Table.SelectColumns(#"Previous Step",{"Col 1", "Col 2", "Col 3", "Col 4", "Col 5"})

Fred

[bbaranyai]

Thanks Fred,

This definitely helps.

However I tried another thing...I referenced the Query in the Query Editor (right click, Reference). I did not make any change in the referenced Query....after Close&Apply, in the Data View the column order of the table of the Referenced Query was perfectly matching the column order of the Query Editor's (both the original and the referenced Query...). 

I am not sure if this is a feature or a bug 🙂

Any idea?

Thanks

Balazs

[freder1ck]

I have no idea. Glad that you got it worked out!

[Anonymous]

I am also having the same problem. It started when I created a new column and deleted the original column that was in the UNION in DAX. Now, as per the previous poster's suggestion, the new referenced table has the right column order but the original one still does not. I even tried using the manual table ordering in Power M step and the data table still shows the columns out of order. Even stranger, the field list on the right is in the correct order as per in Edit Query, but visualizing the table still shows an incorrect order. I can use the reference table instead of the original one in the meantime, but this seems to be still a bug.