Preserve Excel Column formatting on Refresh

[Anonymous]

Hi all

I am consuming data in Excel through an API and using Power Query for some transformations. The columns of the Excel Table output are dynamic and driven by the parameters entered by the user, i.e. the user can enter 1 Customer, a list of Customers or all Customers. 

The problem that I have, is the number formatting is lost each time the parameters are changed and the data is refreshed. As far as I can figure out, Excel is deleted the old columns and inserting new ones after refresh and using the default number formatting. 

I have tried to set the number format in Power Query and changed the parameters of the table to no avail.

Has anyone figured a way to do this or force Excel to accept the format specified in Power Query?

Thanks

Chami

[Anonymous]

Hi Jimmy

Yeah that was the conclusion that I was coming to as well. It is quite annoying that you can set a format in Power Query but Excel completely ignores it.

Thanks

Chami

[Jimmy801]

Hello @Anonymous 

what you mean by ignoring? The formatting in Excel is a reformatting of data, Power query it's treating the data like a database. So it's not ignoring it. Example, if you have a number in Power Query, but you in Excel want to show it as € the value of the cell is only a number in Excel, and so it is in Power Query. The cell value in Excel however is only a number without any text added. When you format it, Excel takes the value and changes the "look" of it. If you want to do the formatting in Power Query, you can do it there (format as Text and change the "look"). However then this field is no longer a number but text field. So you can not make a sum out of it for example.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Jimmy801 · Accepted solution]

Hello @Anonymous 

in your scenario you are changing column names on every query? The formatting should always be connected to column names... so whenever the name is changed or you remove a formatted column and insert it again after one refresh, the formatting is lost for sure. What you could do is to write a makro that is executed after your refresh, that setups the formatting again.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Thanks the Macro was the way to go. Works quite nicely.

Chami