Date hierarchy does not work when connecting to Power BI dataset from Excel

[Anonymous]

Hi everyone

I got a problem when working with dates in pivot table in Excel with connection to Power BI dataset (premium capacity workspace) from Excel. 

Basically, I created a pivot table using the Power BI dataset connection as shown in the screenshot below.

The connection works perfectly well, but when I am trying to work with dates in the pivot table, they do not behave as dates, rather as a text string. The dates are treated by Excel as a string-type field, even though there is a date hierarchy and the data type is set to datetime in the initial Power BI dataset:

Grouping button in pivot table is inactive and the format change through field settings does not work.

Is there a way to somehow transform date fields to get the date hierarchy in Excel (except for creating additional columns in Power BI), or at least to make Excel recognize this data as date?

[Jimmy801]

Hello @Anonymous 

I don't know this platform, but how is Excel getting this data?

Is there power query or power pivot?

In case of this, go to power query/power pivot and change there the datatype.

BR

Jimmy

[Anonymous]

Unfortunately, Excel gets data through the connection. It is processed in the same way as cube data is. I cannot do anything with it in power query/power pivot 

[Jimmy801]

Hello @Anonymous 

the only thing you could do is to probably import your data through power query first (you need exactly to know what you need) then transform it and then visualize it. 

BR

Jimmy