Export data column order wrong for xlsx v. csv

[Anonymous]

Exporting the data from a table/matrix to CSV has the proper column order that matches the table.

However, in the service, export to XLSX seems to randomly re-order the columns.

Shown is an image with all 3 column headers. The service on top, CSV (with matching header order) in the middle, XLSX on the bottom with random order headers.

This is coming from Project Online, however I am able to replicate using multiple data sources including SQL Server.

[v-haibl-msft]

@Anonymous

I can repro the same issue as you. I’ve reported in internally to Power BI Team: CRI 30518765

I’ll post here once I get any update about it.

Best Regards,

Herbert

[uberdube]

I found this 'solution' in another earlier discussion.. but the problem is that the .xlsx field order will export in the order that the fields were originally dragged into the visualisation. To change the order you need to remove the fields from the visualisation and re-add them again in the correct export order. I have successfully tried and tested this, and also found you don't need to blow away all your fields and re-add.. but just remove those to 'backtrack' to the point where the visualisation field order replicates the original 'dragged & added' order (if that makes sense 🙂 

I would be happier to see this is being fixed/changed to allow export of the actual visulisation display order, as I see this as a workaround and not a solution (if it isn't in the pipeline already)...

Cheers,

Greg 

[Anonymous]

@uberdube That's exactly what I found. I went through the "Layout" file within the PBIX and found that's exactly what's happening. The field order there matches the XLSX export, and is also the order in which the fields were originally dragged to the visual.

[v-haibl-msft]

@Anonymous

I've got response from Product team:

Currently this is by design. Export to Excel export the data from the dataset and isn't aware of visual changes that are done in PBI frontend UI like column re order

Best Regards,

Herbert

[Anonymous]

That's a weird "by design" feature.

If anyone knows of an existing enhancement request, please link it here.

I'll try to search before requesting this be changed.

[kdkimble]

I have ran into the same Issue.  This shoudl be by default I think, if you have a table or matrix, the data should export in the same order as it is displayed.  I don't know why you would do this any other way.  This should be changes as this did not used to be an issue to my knowledge, or least I never experienced it.

[Anonymous]

I have just encountered the same issue. I would say this is a bug, because you would either expect the Excel export to follow the column order (in, say, a table visual) completely or not follow it at all. Instead, it goes with the first user-defined order and then ignores subsequent order changes, which seems arbitrary.

My use case is a large dataset with about 120 columns. In the MySQL source view, the columns are organized as we need them to be. Once I load this into PBI Desktop, the columns are sorted alphabetically. I then have to meticulously pick through the alpha list and select each column in the proper order. It's almost impossible to do this without making at least one mistake in the ordering and needing to change it. You then find your final order is not reflected in the Excel export.

[Jitendraddonram]

http://community.powerbi.com/t5/Issues/YearMonth-is-exported-row-wise-instead-of-column/idi-p/327455...