Export Data Does not Retain Formatting

[astano05]

I have a table in Power BI, but when exported to xlsx, it does not retain the column formatting (currency, percentages, decimal places) so it requires additional formatting in excel. When exported as a CSV, it retains all formatting, but I need it as an xlxs. 

I have another report that does not have this issue when exporting, but it pulls from a different data source. The one I'm using is a live azure database connection. 

What could be causing this? Is there a setting that needs to be turned on?

[v-cazheng-msft]

Hi @astano05 

I've done test with it. The format will be exported to xlsx currently. What version of Excel are you using to open the xlsx? You can consider these two methods.

• Export data to CSV and then Convert a CSV File to Excel
• Update your Excel with Analyze in Excel

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[astano05]

Did you test with a live database connection? That seems to be the difference for me at least. With other non-live sources, the formatting is retained for me, but with the live connection it is not. I'm using Excel 365 version 2103.

Exporting as a CSV and converting is a workaround, but I'm concerned about the end-user adoption - and if they add any charts or totals they wont be saved, so I would prefer a straight forward solution if it exists. 

[v-cazheng-msft · Accepted solution]

Hi @astano05 

I can only do test with Live Connection to SSAS or Power BI datasets. I cannot test the Live Connection to Azure Analysis Service with no luck. My test results is are following. I open the CSV and xlsx files with Excel 2016 and I've downloaded the Analyze in Excel updates from Power BI Service.

Here is the result:

1 Live Connection to SSAS

Export to CSV:

Export to xlsx:

2 Live Connection to Power BI datasets

Export to CSV:

Export to xlsx:

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@astano05 , Seems same as this one

https://community.powerbi.com/t5/Service/Formatting-lost-when-exporting-to-excel/m-p/1603457/highlig...

[astano05]

thanks for the reply. It does seem to be the same issue, but the link you provided does not seem to have a solution unfortunately.