Forum Discussion
Power BI -> Pivot Table. Date column returning as text.
Hello I am translating a power BI -> Power Query to empower some of our analysts to perform excel calculations on our data model.
Issue is our time dimension field (Date) returns as TEXT when used in the row field of a pivot table.
I have spent some time looking for workarounds, but it appears that when a field is used in the row section of a pivot table, it is automatically converted to a TEXT data type by default.
One workaround I tried was adding the excel serial # as a field, and using number types such as Int.32 / Int.64 / number in our data model, but this also gets converted to TEXT if placed in the row section of the PivotTable. Note that if placed in the value field it does return as a number, indicating the pivottable autoconverts this.
How can I switch this off? It is not realistic to use the value field for our date dimension and we need to perform date formulas in our excel workbooks.
Thanks,
Zack
9 Replies
v-hashadapuCommunity Support
Hi ZWJ_Create , Thank you for reaching out to the Microsoft Community Forum.
Fields placed in the Rows or Columns area are treated as member captions (labels), so Excel exposes them as text values in worksheet cells, even when the underlying datatype is Date or Whole Number. That’s why both your Date field and Excel serial number return ISTEXT() = TRUE. And yes the same field behaves properly in the Values area. In that case Excel is aggregating the underlying numeric value instead of returning the label text.
As far as I know, there is no setting to disable this automatic conversion for row/column fields in OLAP/Data Model PivotTables. It’s a limitation of how Excel exposes Pivot hierarchy members. The usual workaround is to convert the value back in formulas. If your analysts need extensive downstream date calculations, pull the data into a flat table outside the PivotTable structure (Power Query, CUBE formulas or copied Pivot output), because Pivot row headers will continue behaving as text.
Hi v-hashadapu thankyou kindly for your response.
That is unfortunate to hear but as suspected.
Unfortunately I do need this to work with just PivotTables as the current business analysts are used to this solution, and a PowerQuery/CUBE/ copying pivot outputs would create unnecessary friction.
Hoping there is a native solution to this - and if not currently I think should be implemented as a solution.
Thanks,
Zack- v-hashadapu
Hi ZWJ_Create,
You could definitely raise this in the Microsoft Fabric Ideas. I think it’s a reasonable enhancement request because the current behavior breaks a lot of normal Excel expectations around dates and numeric fields. Since Microsoft does actively review customer feedback there, with enough upvotes these ideas may be implemented as features.
Thanks - I think I will submit one there for completness.
Best,
Zack
- pcoley
Super User
ZWJ_Create did you check if you have data typed those fields as type text (in the steps you used in the etl done in powerquery while preparing the data in the excel file)?
Hi pcoley , yes current data type for the first column is date. The second column I have tried as Int.32/Int.64/number to no success.
Thanks,
Zack
- v-hashadapu
Hi ZWJ_Create , Could you please confirm if you've submitted this as an idea in the Ideas Forum? If so, sharing the link here would be helpful for other community members who may have similar feedback. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum, we’ll be happy to assist.
Thank you for being part of the Microsoft Fabric Community.
Hi v-hashadapu , it appears my original post did not submit.
I have created one today if you could support.
https://community.fabric.microsoft.com/t5/Fabric-Ideas/Bug-Fix-Pivot-Table-Date-column-returning-as-text/idi-p/5264140#M168814
