• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Cell Alignment Not Kept After Refresh|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Cell Alignment Not Kept After Refresh|Power Query|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryCell Alignment Not Kept After Refre…
sp_PrintTopic sp_TopicIcon
Cell Alignment Not Kept After Refresh
Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
1
May 13, 2022 - 7:37 am
sp_Permalink sp_Print

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.  

  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
May 13, 2022 - 10:54 am
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
3
May 13, 2022 - 11:02 am
sp_Permalink sp_Print

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.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
May 15, 2022 - 10:09 am
sp_Permalink sp_Print

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

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
5
May 15, 2022 - 8:54 pm
sp_Permalink sp_Print

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.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
May 16, 2022 - 9:29 am
sp_Permalink sp_Print sp_EditHistory

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

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
7
May 17, 2022 - 5:55 am
sp_Permalink sp_Print

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.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
May 17, 2022 - 8:10 am
sp_Permalink sp_Print

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.

Avatar
ExcelNovice2020
Member
Members
Level 0
Forum Posts: 58
Member Since:
February 8, 2020
sp_UserOfflineSmall Offline
9
May 17, 2022 - 8:57 am
sp_Permalink sp_Print

I tend to agree about the table format itself.  I still think this can be done.  Just missing something.  I do appreciate the continued insight.  

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Kim Knox, Bhuwan Devkota
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
Jessica Stewart: 204
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Bhuwan Devkota
Kathryn Patton
Maria Conatser
Jefferson Granemann
Glen Coulthard
Nikki Fox
Rachele Dickie
Raj Mattoo
Mark Luke
terimeri dooriyan
Forum Stats:
Groups: 3
Forums: 24
Topics: 6221
Posts: 27285

 

Member Stats:
Guest Posters: 49
Members: 31909
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.