Analyz in Excel Data type problem

[EmrecanKn]

I'm encountering an issue where both Date and Integer columns from my Power BI dataset appear as text in an Excel Pivot Table after using the "Analyze in Excel" feature. In Power BI, these columns are correctly set to their respective data types (Date for date columns and Whole Number for integer columns). However, when I analyze the dataset in Excel and create a pivot table, these columns are displayed as text, which prevents proper sorting, filtering, and grouping.

Steps to Reproduce:

1. Create a Data Model in Power BI Desktop:

   • The data model includes a TransactionDate column (formatted as Date) and a LocationNo column (formatted as Whole Number).

2. Publish the Report to Power BI Service:

   • The report and data model are published to the Power BI Service successfully.

3. Use "Analyze in Excel" Feature:

   • I select the "Analyze in Excel" option from the Power BI Service to work with the data model in Excel.

4. Create a Pivot Table in Excel:

   • In Excel, I connect to the dataset and create a pivot table. I add both the TransactionDate and LocationNo columns to the pivot table.

5. Issue Observed:

   • Both the TransactionDate and LocationNo columns are displayed as text fields rather than their correct data types (date and integer, respectively) in the pivot table. This makes it difficult to perform operations like sorting, filtering, or grouping based on these columns.

[v-zhengdxu-msft]

Hi @EmrecanKn 

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Check their column type in Power Query, not in Power BI.  They may be variant (ABC123).