cancel
Showing results for 
Search instead for 
Did you mean: 
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

7 REPLIES 7
Eyelyn9
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

Eyelyn9
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 @Eyelyn9 

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.

View solution in original post

Anonymous
Not applicable

Great, thanks for sharing @frano72 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors