Solved: Analyse in Excel and Date Format

Claude_ · ‎03-09-2017

Using "Analyse in Excel" I do understand that we have to define measure to be able to sum values.

I didn't find any guidance on how to handle date. Once in Excel, date member are like ordinary member and I cannot apply timeslicer on it. Is that a limitation? Is there an alternative?

Note: In the power bi Desktop and Services, I use a date table linked to data.

Regards

Claude

Anonymous · ‎03-09-2017

Hi @Claude_,

I can reproduce you issue. Based on my observation, the issue is caused by that Power Pivot treats Date columns as text. I find a connect item and several threads stating the issue that PowerPivot treats date columns as text, I would recommend you vote the connect item and check the following similar threads.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c0253620-6ae0-46ea-8b6e-124e6c950239/powerp...

http://stackoverflow.com/questions/27913051/powerpivot-incorrectly-convert-date-value-to-text-in-piv...

Thanks,

Lydia Zhang

View solution in original post

Anonymous · ‎03-09-2017

Hi @Claude_,

I can reproduce you issue. Based on my observation, the issue is caused by that Power Pivot treats Date columns as text. I find a connect item and several threads stating the issue that PowerPivot treats date columns as text, I would recommend you vote the connect item and check the following similar threads.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c0253620-6ae0-46ea-8b6e-124e6c950239/powerp...

http://stackoverflow.com/questions/27913051/powerpivot-incorrectly-convert-date-value-to-text-in-piv...

Thanks,

Lydia Zhang

Anonymous · ‎05-01-2020

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.