cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
erhodes
Advocate II
Advocate II

Analyze in Excel - Date field issue

When using 'Analyze in Excel' Date fields from my model show up in excel as text which really limits the usage of the Analyze in Excel functionality. Has anyone else encountered this and come up with a solution. 

15 REPLIES 15
Poma
Frequent Visitor

Has anyone solved this problem? Is it addressed by Power BI?

mmace1
Post Prodigy
Post Prodigy

I just want to resurrect this - we just ran into this issue today:  Analyze in Excel doesn't treat date fields as dates, and thus my boss can't group things as he's accustomed. 

I'm having the same issue. Very limiting

Hello everyone.

 

I'm coming from https://community.powerbi.com/t5/Service/Analyse-in-Excel-and-Date-Format/m-p/1061012#M94618

 

Is there any way to force columns to date format ?

 

I have many dates formatted as date in powerbi that powerpivot with analyse in excel reads as text and I want to filter them using power pivot date filters.

 

One workaround could we to load the data to power query instead of connection to the pbiazure and changing to date there. But I don't think this is possible, right?

How does this only have 5 votes?  C'mon people, you know what to do!

axel_BI
Helper I
Helper I

It is 2020 and still no solution for this?

 

A pivot-table with only string values makes the whole "analyze in excel" function pretty obsolet.

 

I need to be able to sort, filter and sumif sales relative to the date column and a storenumber column. As there will be up to 300.000 rows there is no other option in sight.

 

rwb392
New Member

Not to necropost, but I am currently running into this issue and have so far been unable to find a solution. A Power BI field that is typed in Power BI as a Date and formatted to show up on reports as "Tuesday, December 10, 2019" pulls into my PivotTable (from Analyze in Excel) as a String instead of a Date.

 

Is there any way to get Excel to treat the PivotTable date field as an actual Date?

 

 

ExcelABC
1Fiscal DaySome Measure`="Fiscal Day"+1
2Monday, May 15, 20173#VALUE!

 

Here's an example of what I'm talking about. If "Fiscal Day" were an actual date, Fiscal Day + 1 would be Tuesday, May 16, 2017 (or however you choose to format your dates) but because it's a string, you get an error.

 

This also makes things like =SUMIFS(Some Measure, Fiscal Day, ">"&DATE(2017,1,1)) not work.

 

Any help appreciated, although I would prefer to not make a column D in the above example that essentially has:

 

=DATEVALUE(
   MID(
      $A2,
      FIND(" ",$A2),
      99
   )
)

 

 

prbkb
Regular Visitor

I am facing the same issue

AndrewSEA
Advocate II
Advocate II

Following.  This is a major issue with Analyze in Excel.   Can't filter the date range because it's a text string.

Anonymous
Not applicable

Same issue here. I would like to use a Date field with Excel's Pivot Table Timeline feature, but when I attempt to create a Timeline, the following message appears: "We can't create a Timeline for this report because it doesn't have a field formatted as Date."

 

The field is formatted as a Date in Power BI, but using the Analyze in Excel feature disregards this formatting.

 

Date Issue Power BI.JPG

mahimabedi
Responsive Resident
Responsive Resident

Facing the same issue. Is there a fix or workaround for this?

Eric
Helper I
Helper I

Has this issue ever been resolved?  I am connecting to the PowerBi service (OLAP) and I am having the same issue with dates showing up as text in pivot tables.  

landoncope
Frequent Visitor

I'm facing the same issue. I'm looking into PowerBI for the company I work for, largely because of the "Analyze in Excel" feature. I agree that not having dates working properly severely limits this feature's usefulness.

 

The obvious workaround is to create separate day/month/year fields in the dataset, but this is not ideal as it leads to confusion for end-users and broken reports down the road once they fix this issue (and remove said fields), etc.

v-yuezhe-msft
Microsoft
Microsoft

Hi @erhodes

Based on my test,  PowerPivot changes the format of date columns to General when I use “Analyze in Excel” feature. From my point of view, the issue is related to PowerPivot, I find a connect item and several threads stating the issue that PowerPivot treats date columns as text, I would recommend you post the question in the Power Pivot forum to get dedicated support.

And you can also check the following similar threads.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c0253620-6ae0-46ea-8b6e-124e6c950239/powerp...
http://stackoverflow.com/questions/27913051/powerpivot-incorrectly-convert-date-value-to-text-in-piv...

 

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

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

Top Solution Authors
Top Kudoed Authors