Analyze in excel converts dates to text

[johndolan2]

Hi - Analyze in excel converts dates to text. Is there a fix to this?

Thanks,

John

[lbendlin · Accepted solution]

Ah - you didn't mention the date hierarchy. I wouldn't do that if I were you. I always prefer to use a dedicated (and declared) dates table in my data models.  Maybe you can give that a try.

[johndolan2]

I had started to wonder is I should have created a date table. A year ago when I uploaded the single spreadsheet it just did not cross my mind. Thanks very much.

John

[lbendlin]

What actually happens is that the Power BI UI quietly (or on your instruction) treats a text field as date.  But Analyze in Excel connects to the underlying data model where that field is still text (or variant).  Change the field type in your data source, or in the Power Query M code.

[johndolan2]

Thanks for the reply. I must be misunderstanding something ... because I definately have the field set as date, in my source spreadsheet, Power Query, and the model. I also have a date hierarchy. It is only when using Analyze in Excel that the date hierarchy goes away and the dates can no longer be grouped. 

Thanks again, John