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

Copper Contributor

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. In the External Table Data Properties I checked the box for Preserve column sort/filter/layout so that these formatting selections are applied to all records in the table when the data is refreshed.

The first time I did this, it was fine. The following month is when I had a problem. When I refreshed the data connection, the columns that changed names ended up being moved to the far-right end of the table. I think this was due the effects of the "Preserve column sort/filter/layout." My guess is that since the column header name changed, it is interpreted as being a new column.

When I uncheck that box and refresh the data the column order does not change, which is what I want. However, only the first record retains the desired cell formatting. The remaining records have a different formatting.

What approach will result in keeping the desired column order and the desired formatting throughout the whole table when I refresh the data?

0 Replies