Forum Discussion
Date corrupt in Featured Table / Excel Data Types
- 4 years ago
I just found the incorrect date format and found a workaround. I do believe this is a bug and not working as intended.
I went into Format Cells in Excel, and found the formatting code that is causing this to display incorrectly.
sho\rt d\ate
I went back to Power BI Desktop and changed the date format from Short Date to m/d/yyyy for one of the date fields:
And that fixes it:
I looked at the columns in Tabular Editor external tool. If you select Short Date or Long Date in Power BI Desktop, this is how it sets the Format String:
These look find in Power BI Desktop, but do not display correctly for Excel Data Types.
Here are the Format Strings that work correctly (though the examples in Tabular Editor are also wrong):
In Power BI, they all look fine:
ā
So to summarize, if you select the Short Date or Long Date date formats in Power BI, they will not display correctly in Excel Data Types.
The workaround is to select any other date format, including the ones represented by the Short Date and Long Date options (eg. m/d/yyyy and dddd, mmmm d, yyyy).
Will Microsoft consider correcting this behavior? At a minimum, the Excel Data Types documentation should say that the Short Date and Long Date formats do not work with this feature.
Rudz Any updates?
I didn't realize I could paste in pictures. Maybe this will help.
These are the dates in Power BI Desktop in the Data view:
Here are the dates in a report in the Power BI service in My Workspace:
If I click Analyze in Excel, these are the dates in a Pivot:
If I open a blank spreadsheet in Excel, type in the key and select the Data Type, this is how the dates look:
Back when I originally set this up, they displayed correctly.
Here is a date working correctly in a different Data Type from My Workspace:
I changed the Row Label in the table in Power BI Desktop and republished. The dates look the same:
If I add the dates via formula (eg. =A2.[RO Date]), it looks like this:
The Excel number type is "General". If I change the Excel number type to Date, it displays correctly.