Excel get data from Power BI doesn't recognize Dates as Number and cannot be sort.

[Hsilva]

Hello community, 

I have a pivot table connected to a Power BI data model (get data from Power BI), in the data model the Month-Name and MM-YY columns are sorted by another column and woks perfect. The problem is in excel pivot table when I sort the dates in a different way to the data source order it doesn't work ( for any of date fields: date , month, period or MM-YY). I understand excel doesn't recognise Date data from Power BI as a number, but this is happening also with month number or period.

Any suggestion?

Thanks in advance

[Hsilva]

Hi GilbertQ, thank you for your help.

Yes; they are number, but the calendar table was created in Power Pivot in excel (not in power query).

this model was initially created in Excel then imported to Power BI.

does that answer your question?

Thank you.

[GilbertQ · Accepted solution]

Hi @Hsilva 

What I have found is that you need to create a date table and link your date column to the date table. Then make sure to mark your date table as a Date TABLE.

Set and use date tables in Power BI Desktop - Power BI | Microsoft Learn

Then once that is done and you have uploaded your Power BI report to the service, then connect with Excel and it should work with the dates.

[Hsilva]

Hi Gilbertm thank you for your help.

yes I have calendat table with a dete column (market as a date table) adn also linked to the date calumn from the fact talbe.

[GilbertQ]

Hi @Hsilva 

Can you confirm that even though your column might have a number in it, is the data type set to whole number?