Maintain formatting after refresh of data in Excel table via Power query

[DanielBaker]

I have a table in Excel that refreshes regularly based on Power query. When it updates, as well as the data changing the column headers change - becuase the column headers are the dates for the coming 12 months (more specifically the first working day of each week). Unfortunately the formatting for any new weeks also disappears, and I believe that this is because formatting is linked to the column header.

Does anybody know of a way to keep the formatting, that does not use VBA? I know how to do this with VBA, and will go down that route if I have to, but I would prefer to avoid it.

[ronrsnfld]

What exactly do you mean "formatting disappears"?

Is it the formatting of the column headers? or the formatting of the column contents?

Column headers are always text strings, and you can format that string in Power Query.

Column contents format may be trickier depending on the type of data in the columns.

Excel will format the column contents based on the content of each cell.

[DanielBaker]

It's the grid colour and font colour of both the header and the contents. It also removes the conditional formatting from the contents

[ronrsnfld]

Thanks for clarifying.

I'll look into it later. (don't have time right now).

[DanielBaker]

Thanks - and it's not urgent because the VBA solution I have implemented is working

[ronrsnfld · Accepted solution]

Unfortunately, I think you are correct in that you will have to continue your VBA solution. Perhaps using VBA to refresh the query and then, when that has been done, to restore your formatting.

And I did not fully understand your meaning when you wrote "formatting is linked to the column header." That is the case when dealing with tables generated by Power Query.

The font style, color, cell interior color, and the color and nature of the grid lines can be created with a Custom table style, and will be used when new columns are created. Unfortunately, it does not seem as if Conditional Formatting is a feature of the TableStyle object.

[DanielBaker]

Okay

Thanks

[rubayatyasmin]

Hi, @DanielBaker 

try to use conditional formatting, table formatting, or use Preserve cell formatting on Update option in excel. 

[DanielBaker]

I have already tried those and it doesn't work