Forum Discussion
Exporting custom formats with current layout
Hello,
Whenever I export data with the current layout to excel, any custom format for example I have a custom format for time 00:00:00 - on the excel exported it does not include my custom format, instead the format shows as 000000.
Is there a way to keep my custom format when exporting with current layout, if I were to export with summarized layout my custom format is kept.
6 Replies
Hi micpete - This issue arises because Power BI exports are limited in how they transfer formatting to Excel, particularly with custom formats in "Current Layout."
You mentioned that exporting in "Summarized Layout" preserves the format. If your data needs and formatting requirements allow, this may be the simplest solution, as Power BI better preserves custom formatting in this layout.
Although not ideal, you could create an Excel macro or use conditional formatting rules to reapply the custom format upon opening the export file. If this is a repeated task, automating it within Excel might save time.
TimeFormatted = FORMAT('Table'[TimeColumn], "HH:MM:SS")
This way, the custom format is saved as text, which might carry over better during export. However, this method will treat the values as text, not numbers or time, in Excel.
Ideas - Microsoft Fabric Community
if it not works, raise it as an idea by using the above link
Hi all,
I should have mentioned that I'm using a measure for calulating total time, I've had to convert time to seconds then a calcuation to sum each hour, minute, second total, so I don't have the option to change the data type to time as the value presented looks like a time value, example return output could be 2160800 which I've then used the custom format option in power bi to ":" to separate the value into hours, mins, seconds so that it looks like 216:08:00. I've tried using TimeFormatted = FORMAT('Table'[TimeColumn], "HH:MM:SS") as you mentioned in your post but I get an error message.
Hi micpete
I did the same thing as you, but it works well too.
If possible, could you please share some raw data(exclude sensitive data) or create some sample data. It would be helpful to find out the solution. You can refer the following links to share the required info:
How to provide sample data in the Power BI Forum
How to Get Your Question Answered Quickly
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi micpete
In my test, this feature works well:
In Excel:
And here are some steps for your reference:
Make sure your time data in Power BI is correctly recognized as `Date/Time`. Sometimes, this alone can solve formatting issues.
If all else fails, you can apply the custom format manually in Excel after exporting. Select the time column, right-click, choose **Format Cells**, and set the custom format to 'HH:mm:ss'.
Just as rajendraongole1 posted that you can create a new calculated column in Power BI that formats your time as text. This can help in preserving the format when exported.
FormattedTime = FORMAT([YourTimeColumn], "HH:mm:ss")Hope these steps can help mitigate the issue.
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hello,
This is what I get when exporting the file with current layout to xlsx:
2024 Filter 180 1924450 2123937 1973043 1925540 I am using the custom format option in power Bi to change the way time looks to the below format, I have tried changing the custom format to the way you have formatted it and it's still giving me the same result.
When I export the data I do also receive this error when opening the file:
Hi micpete
I've done a lot of testing, but I've never had a problem like yours. If possible, please share your PBIX file for further testing. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
At the same time, you can also go to the Excel forum to post and see if there is a problem with Excel.
Excel | Microsoft Community Hub
Best Regards
Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
