Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.