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

24 REPLIES 24
CLEG
New Member

It is quite simple: In your Dataset (Semantic Model) , in your calendar, create a column DateSerial = VALUE ( FORMAT Calendar[Date],"00000" )).  Then in your Dataset (Semantic Model) measures, add a measure DateSerial = Max ( Calendar[DateSerial] ).  When you pull your data into Excel (Analyze in Excel), pull both value into your "pivot": Calendar[Date] and the measure [DateSerial].  Very important to bring the measure because Calendar[DateSerial] will be a string.  And very important to bring the Calendar[Date] as well because even though it is a string and useless, it defines the date granularity required to show the measure DateSerial properly.

j_ocean
Helper V
Helper V

In the data model you can do several workarounds:

  • Use formaty YYYY-MM-DD, which numerically or alphabetically sorts correctly.
  • Crerate a Delta-Days column, just today - date, creating a sortable integer value.
  • Create a custom dates table with all the possible breakouts you could ever want, like quarter, Delta-Days, etc. 
roncruiser
Helper V
Helper V

This is not good.  This has to be fixed.

When using Analyze in Excel:  When I pull in a Power BI dataset via Excel, columns defined as numbers come in as text.  When I drag a numerical column into Rows of a Pivot Table Field, the numbers are now seen as TEXT.  The option to sort the column is A-Z or Z-A.  The sort option should be Smallest to Largest or Largest to Smallest.

Is there any solution to this on-going issue?

user9877
Frequent Visitor

As of December 2022, this issue still exists! 

 

What's the actual use of the Analyse in Excel/ Power BI connection if you can't:

- Create measures

- Or at least retain field numerical formatting given you can't create measures.

 

Essentially, if there's any new analysis that someone wants to do Excel side - it would have to be done from Power BI which is very reduntant, and gives the Excel user vertually no extra independence. 

I have this issue too, it's very annoying.

While searching on Google, I found a possible workaround for this issue https://medium.com/@curtisrstallings/date-hierarchies-power-bi-analyze-in-excel-96431f735678

I didn't tested it yet but it seems a good alternative

edhans
Super User
Super User

To anyone having this issue, can you test this for me? I have no problem with this working, but this comment made me think:

edhans_0-1656108233042.png

 

My date fields are always formatted as Short Date - so mm/dd/yyyy for US. I am wondering if that long normal format is confusing Excel and it thinks it is text. If yours is formatted like that, can you change it to the Short Date format, republish, then see if analyze in Excel works correctly? Here I am using the date field in the Timeline slicer just fine, and showing the date format in the 2nd matrix in columns.

edhans_1-1656108324559.png

 





Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Poma
Frequent Visitor

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

mmace1
Impactful Individual
Impactful Individual

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

Anonymous
Not applicable

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?

Anonymous
Not applicable

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.

 

The other option would be using PBI service...

 

If analyze-in-excel is part of the planned enterprise PBI architecture, data models need to be built for it e.g. creating a measure summing your dollar values and having dates broken out to their higher aggregations. Then it should work.

Agree!  Analyze-in-Excel is an important part of the whole Fabric platform.  Numeric values seen as text is a very odd issue to miss when sourcing a Power BI dataset from the service into Excel.  I am thinking it is by design but cannot think of a valid reason why it would be.  If there is no valid reason, then why has it not been fixed?  This has been a long standing issue.

What I mean is, if you as an organization intend to support A-in-E you need to design for it. It's a transitional capability that introduces complications in managing the capacity, I wouldn't expect them to put many resources into fixing it.

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

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