Forum Discussion
Importing data into pivot excel from SSAS Tabular, wrong data type
Excel does not convert the data to PivotTable as I expect, columns that are Date type are treated as Text, which prevents me from additional column filtering (by date functions), has anyone encountered a similar problem?
I import data from SSAS Tabular into Excel using Get Data -> From Database -> From Analysis Services and display them in a Pivot Table.
I checked the column format in the database/SSAS Tabular, it is for example 14.12.2020. 00:00:00 (Date and time), in the PivotTable there is only text.
I couldn't change the column type after importing into the PivotTable, not sure if that's even possible, is the PivotTable just an end user view, a visualization?
In the last pop-up (Import data) during import, I tried to uncheck "Retrieve data and errors in the office display language when available", but it did nothing in that regard.
I came across a workaround using PowerQuery, more precisely, Get Data -> From database -> From SQL Server Analysis Services Database (Import). In PowerQuery I changed the column type to date and after that I created a PivotTable referenced to the imported one. And it worked. However, this workaround is not suitable for me.
Thank you.
2 Replies
That's good practice to ensure all fields data types are declared to final step in Power Query. Otherwise that could be issues like this plus you could slightly improve performance. And that's not a bug, that's by design - data model has no idea what do we assume using "any" data type, treats all such fields as texts.
You must edit the data source of the pivot table and this should open the Power Query pane. Here you must set the correct data types for all columns.
