Log In
Register
Home
February 8, 2020
Offline
Within my table, I have the even rows with a cell alignment center (horizontal) and bottom (vertical). I have the odd rows with a cell alignment center (horizontal) and top (vertical). Boxes checked to "Preserve column sort/filter/layout" and "Preserve cell formatting."
Upon refresh of the query, with no additional rows added to the table, no issues. However, upon refresh of the query and added data/rows to the table, the even rows revert to the same cell alignment of the odd. Instead of center/bottom, they are center/top.
What am I missing that is causing the cell alignment of the data to change when additional rows are added to the existing table? Thanks.
July 16, 2010
Offline
When Power Query gets new data it also gets the original data again, so in effect it's replacing the data that you've formatted. Not sure why it's not respecting the preservation of cell formatting though. Maybe cell alignment isn't in the list of formats it preserves.
February 8, 2020
Offline
That is what is odd. I am fairly certain PQ does preserve the alignment, assuming the option is selected. It preserves the alignment if the rows within the table are the same. But when I do above, it does not. Again, odd.
July 16, 2010
Offline
It's preserving the alignment if the rows within the table are the same because it's not actually loading anything to Excel because there's nothing to update. It's an illusion that the formatting is preserved
February 8, 2020
Offline
All other formatting within the table is preserved with each update/data add. It is just the horizontal cell alignment of the even rows reverts back to the horizontal cell alignment of the odd rows when data is added.
I have a specific table design assigned to this table but could not see where that was the issue as I removed all designs and the alignment still reverted back.
This is the first time I have come across PQ not preserving a certain format when it seems like it should.
July 16, 2010
Offline
I agree it seems like it should, however the fact that it doesn't when it preserves all other formatting suggests to me that maybe cell alignment formatting was omitted from the coding in Excel when they built the feature, therefore I doubt there is a fix. Of course it could also be by design because data types have a default alignment as follows:
Text - left aligned
Numbers - right aligned
Mynda
February 8, 2020
Offline
Not sure. It just seems odd I've never had alignment issues prior (they were always preserved) but here it maintains the proper alignment in the odd rows but not the even.
For now, I've just duplicated the table and copy the formats as needed. I still think something is "off" but it's a work-around.
July 16, 2010
Offline
Thinking about this a bit more, I wonder if it's a Table feature. Tables are designed to copy formatting and formulas down a column. This is best practice for tabular data. It might be the table that's overriding the formatting and it's being triggered when a refresh that results in the data changing is applied to the table.
1 Guest(s)
