Forum Discussion
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
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?
Has anyone solved this problem? Is it addressed by Power BI?
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.
Following. This is a major issue with Analyze in Excel. Can't filter the date range because it's a text string.
I am facing the same issue
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
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/powerpivot-dates-turn-to-text-in-excel?forum=sqlkjpowerpivotforexcel
http://stackoverflow.com/questions/27913051/powerpivot-incorrectly-convert-date-value-to-text-in-pivot-tableThanks,
Lydia ZhangI'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.
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.
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.
