cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MichaelJSchulz
Frequent Visitor

Keep Column order with changing column names when exporting to Excel and preserve cell formatting

I receive a new Excel file each month. The names of two columns in that file will change each month because part of the column name references the month name.

 

I use Power Query to extract the data from the file, perform some transformations, and load the results to a different Excel file. The results are loaded into that Excel file as an Excel Table. I formatted the Table columns with appropriate Types and desired formatting e.g setting Text or Number etc, Left alignment, Font size, and so forth. So that these formatting selections are applied to all records in the Table, in the External Table Data Properties, I checked the box for Preserve column sort/filter/layout

 

The first time I did this, it was fine. The following month is when I had a problem.

 

When I refreshed the queries, the columns that changed names ended up being moved to the far-right end of the Table. I think this was due in part to the effects of the "Perserve column sort/filter/layout." When I uncheck that box, the columns stay in the desired order. The columns with headers that changed do not move to the far-right end of the Table. However, only the first record retains the desired formatting. All other records have a different formatting.

 

What approach will result in keeping the desired column order and the desired formatting throughout all Table records? 

2 REPLIES 2
Vijay_A_Verma
Super User
Super User

In pivot table options, Preserve Cell Formatting on Update should be checked. 

It's not a pivot table. However, the Perserve Cell Formatting checkbox is also available the External Table Data Properties.

 

If it is checked, then the column order is not preseved due to the changeing header name of two columns.

If it is unchecked, then the cell formatting is not applied to new records in the table when the table is refreshed. 

 

What approach will result in keeping the desired column order and the desired formatting throughout all Table records?

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors