Calculated column from Data Model order in Worksheet

[robinherrick]

I have a DAX column in the Data Model table which comes through to the Worksheet as the last column even though I have moved it in the Data Model view. I can move the column in the Worksheet but it goes back to the last position when I refresh the data. Can someone tell me what I'm doing wrong please?

[#2]

@robinherrick 

Could you please clarify bit more how do you add data to data model (add to data model table, Power View connector, Power Query); adding the column do you add it only in Power Pivot or not; how do you return table from data model back to Excel sheet.

[#3]

@Sergei Baklan 

Data is from Power Query, loaded to worksheet and data model.

Enter Data Model (in Power Pivot) to create DAX calculated column.

Column appears in worksheet on Refresh.

I don't know any other way to get a table from the Data Model into a worksheet.

[#4]

@robinherrick 

I see, thank you.

Okay, if we Power Query left table, load to data model and return to Excel

adding in the middle any calculated column in the middle

first will be returned all query columns and after that additional ones. Moreover, Power Pivot keeps the same order if check tree view

I'd open Data->Existing Connection and select any existing table at any place, but better not one returned by Power Query. That could be our source table, but I created this small Table2

Open and save result into Excel sheet. That will be duplication of our Table2, in background it will be added to data model as well.

Right click on this table, from menu Table->Edit DAX, from drop-down menu in appeared window select DAX

and instead of Table2 enter DAX expression here as

evaluate
summarize(
Table1,
table1[a],
table1[e],
table1[b],
table1[c]
)

In general that could be any proper dax query which we evaluate here. Result is

Perhaps in first case EVALUATE in background works as with ADDCOLUMNS, when there is no way to change order of added columns, but that's only the guess.