Showing results for 
Search instead for 
Did you mean: 
Helper V
Helper V

Analyze in Excel Changes Data Types

I publish a dataset from Excel using Publish to Power BI -> Export.

When I access the Dataset from the Power Bi service utilizing Analyze in Excel, the number data types are changed from whole numbers to some type of Text format.  When I drag numerical columns into either the Columns or Rows of a Pivot Table, these are seen as a format other than Numbers.  This changed data type cannot be sorted with Sort Smallest to Largest or Sort Largest to Smallest.  Only can be sorted using Sort A-Z or Sort Z-A which not how I want to sort.


Is this normal?  Is there a way around this? 


I understand you have to use a measure to aggregate for the Values but measures do not work for Columns and Rows.

What's going on?  

Helper V
Helper V


I tried your suggested settings.  Unfortunatley, the suggestions did not work for me.

I'll document what I am doing in more detail.  I will re-post soon.

Community Support
Community Support

Hi @roncruiser ,


Based on testing, I did not reproduce your problem.


The data source I used for testing was EXCEL. This is the table I tested, column 1 is imported from the data source, column 2 is calculated by column 1, column 3 is calculated by the RAND () function, I set their data format uniformly as whole number.


In the excel analysis they are also all sorted numerically.


Here are the possible related settings in excel for your reference.





I attached my test PBIX at the end, you can also use it to test to see if the data type still changes. You can also share some details of your test environment at the same time, thanks in advance.


Best Regards,

Community Support Team


If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data


Also, after you retrieve your data in Excel, drag Column2 into rows and then sort the column.  You will find you cannot sort numerically.  Not Largest to Smallest or Smallest to Largest.


I've documented my process to reporduce the error:

I start inside Excel.  I create M code to extract data from .csv files.  The data is loaded into the local Excel Data Model.  I do not use Power BI Desktop at all during this particular process.

I then publish the same data to the Power BI service->


Excel confirms that the Workbook is exported successfully.
3-WB published successfully.jpg

I then go to my Workspace inside of the PowerBI Service and click on the Dataset->
4 goto dataset.jpg

I then click on Analyze in Excel.  It then verifies that an Excel is ready and sends to my Downloads folder.
5-Analyze in Excel.jpg

I then open the Excel file from my Downloads folder.

The data is in text instead of whole numbers.  I captured here:
sort a-z.jpg

When I sort A-Z or Z to A, it is weird.

The following is sorted Z-A.
sorted z to A.jpg

The following is sorted A-Z
sorted Az.jpg

It should look like this:
sort smallest to largeset.jpg

Sort Smallest to Largest and Sort Largest to Smallest work perfectly for numbers.

For this entire exercise, I do not use PowerBi desktop or service to visualize the data.
I only use the service to store the data and retrieve it again using Excel.



Helper V
Helper V


I've not tried that yet.

What I'm trying to do is publich data from Excel to Power BI using the Export workbook data to Power BI feature in Excel.  
Screenshot 2022-05-16 090503.jpg

Re-accessing that same Dataset in the Power BI service using Excel and the datatypes are different.

Screenshot 2022-05-16 091227.jpg

Re-accessing the Dataset using Analyze in Excel is easier for some of my colleagues but the change of Data Type is frustrating to get around.

Is there a setting in Excel to change I am unaware of I just cannot find?


Super User
Super User



Did you already try to change the country configuration in Power BI?

Maybe its reading the data in different format.




Regionality settings break me down lately.





Helpful resources

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

Ted's Dev Camp - October 6, 2022

Watch the playback of Session 26 with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors
Top Kudoed Authors