08-13-2020 08:44 AM
08-13-2020 08:44 AM
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?
08-13-2020 02:36 PM
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.
08-14-2020 02:58 PM
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.
08-15-2020 02:41 PM
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.