Forum Discussion

roncruiser's avatar
roncruiser
Post Patron
4 years ago

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 num...
Need Help
Show and Tell
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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,
Gao
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

roncruiser's avatar
roncruiser
Post Patron
4 years ago

Anonymous 

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.


I then go to my Workspace inside of the PowerBI Service and click on the Dataset->


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


I then open the Excel file from my Downloads folder.

The data is in text instead of whole numbers.  I captured here:


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

The following is sorted Z-A.


The following is sorted A-Z


It should look like this:


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.

Thanks,

Ed