Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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, 

 

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

View solution in original post

8 REPLIES 8
v-eqin-msft
Community Support
Community Support

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
Community Support
Community Support

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
Not applicable

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. 

 

 

 

 

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

Anonymous
Not applicable

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. 

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.

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
Not applicable

Great, thanks for sharing @frano72 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors