MichaelSchulz
Board Regular
- Joined
- Apr 10, 2014
- Messages
- 64
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 in part to 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?
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 in part to 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?
