Date corrupt in Featured Table / Excel Data Types

[Rudz]

I have set 2 tables as "Featured tables" in my data model and published to My Workspace. The tables show up in Excel as Organizational Data Types, and I am able to link cells to my tables. I set this up in July for a demo and everything was fine. 

Today I refreshed my data for another demo, and the date fields have gone crazy. I've since gotten a new laptop, so that could be part of the problem. Instead of displaying a date in English, the date shows as "00ort 30at2021" for 11/30/2021 and "00ort 31at2021" for 10/31/2021. I'm assuming this is some odd long date format for another language. Excel, Power BI Desktop, Power BI Service, and Windows options show English as my preferred language. I can see the dates in Short Date format (m/d/yyyy) in the model and in a report page in Power BI Desktop before publishing, and it looks the same in the report in the service. But in Excel, the Data Card and fields are defaulting to some other language or format. When I retrieve the field in a cell (eg. =C22.[Date]), the Excel cell formatting type shows "General". As soon as I change it to Date, it corrects back to English dates in the cell, but the Data Card is still in the odd format.

Where is the date format coming from and how do I fix it? Is this my setting, or a bug?

[v-janeyg-msft]

Hi, @Rudz 

I have already reported this issue.

This issue has been submitted internally. Internal ICM ID:277143136 Please be patient to wait for fixing. If there is any news, I will update it here.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

@Rudz  Any updates?

[Rudz · Accepted solution]

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]

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.

[Rudz]

It looks like a normal date everywhere except the Excel organization data card. I can change the date formats in Power BI, for example, from long date to short date format.

[v-janeyg-msft]

Hi, @Rudz 

I did a simple test and didn't find any abnormality in the date column. Consider whether there is a problem with the data source.

You need to confirm whether only one report has the problem.

Also you can try to reconnect it. If you modify the column nameand other settings, it may have an impact.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[v-janeyg-msft]

Hi, @Rudz 

Is the date column in the data source(or pbi reports) normal? Does this problem occur in one report or in each report?

I will also test it here, if it’s a bug, I’ll report it.

Best Regards,
Community Support Team _ Janey