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 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.