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?
