Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Preserve Excel Column formatting on Refresh

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.

 

Chamih_0-1608095912323.png

 

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

 

Thanks

 

Chami

1 ACCEPTED SOLUTION
Jimmy801
Community Champion
Community Champion

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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

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
Community Champion
Community Champion

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
Not applicable

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

 

Chami

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors