[Analyze in Excel feature] Date are not recognized in Excel file

[Anonymous]

Hi community, 

I am using Analyze in Excel functionality of Power BI published reports. When I open the Excel file and PivotTable fields unfortunately I am having issues with a column which was a has a date format in Power BI (m/d/yyyy), however in Excel it seems it is nor recognized as date, instead it is a text. 

I can see this issue was discussed in prerviously

-https://community.powerbi.com/t5/Power-Query/Analyze-in-Excel-Date-field-issue/td-p/139812

-https://community.powerbi.com/t5/Service/Analyze-in-Excel-causes-problems-with-dates/td-p/425169

However I can not find a solution/workaround. 

Will be grateful, if you could share your experience and advice on this issue. Many thanks in advance. 

Sincerely, 

[v-eqin-msft]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @Anonymous ,

Currently you may consider using the following way to work around it:

1. Inserting a PivotTable from your table in Excel work sheet instead of PowerPivot window.If your data source is from SQL Server, you may first import it into an Excel worksheet.

2. Using FORMAT([Date],"yyyyMMDD") to create an additional column in PowerPivot table. Then you can use this column in your PivotTable for label filter.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c0253620-6ae0-46ea-8b6e-124e6c950239/powerpivot-dates-turn-to-text-in-excel?forum=sqlkjpowerpivotforexcel

https://community.powerbi.com/t5/Service/Analyse-in-Excel-and-Date-Format/td-p/139630

https://community.powerbi.com/t5/Service/analyze-in-excel-dates/m-p/40849

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,
Eyelyn Qin

[Anonymous]

Dear @v-eqin-msft 

thanks for your reply. 

Unfortunately I can not confirm that your suggestions helped me solve the problem as

1. My datasource is not SQL Server

2. I can not create additional column in PowerPivot Table as the respective field is inactive/greyed out. 

I found a reference that suggests that one cannot create a calculated field or a calculated item in a PivotTable based on OLAP source data. https://social.technet.microsoft.com/Forums/en-US/39aa92e1-0d41-4331-8b4f-5268c2f1b442/why-is-quotca... 

Will be very grateful if you could suggest other potential actions for this issue. 

[frano72]

Hi @Anonymous - did you end up finding a solution to this ?  I have the same issue.  Thanks !

[Anonymous]

Hey @frano72 . Unfortunately it was not possible to find work around on this. I have to use excel formulas in downloaded file to derive time intelligence formulas and calculations there. 

[frano72 · Accepted solution]

Hi @Anonymous - I managed to get it to work and show dates in Excel.  The trick was to "mark as date table" in Pbi Desktop and then you get to choose a column as a date column.  After I did this, it then appeared in Excel !  If you have multiple date columns per table that you want to use, then you might be out of luck....but most use cases need only one.  I then used the timeline in Excel to create the ability to select slices of time.

[Thomas_Mineo]

Thanks a lot,

After hours of searching, this solution was what finally worked.

I had to create a manual Date Table with the DAX function "Table = CALENDARAUTO()".

This table was the only date field recognized by excel as a date and not text. I then linked the new table to the date field in my original table, and I could finally filter by date.

Crazy that such a literal malfunction is left in the final product even years after the problem was brought up. It must be really expensive to solve.

[Anonymous]

Great, thanks for sharing @frano72