cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
roncruiser
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?  

6 REPLIES 6
roncruiser
Helper V
Helper V

@v-cgao-msft 

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.


v-cgao-msft
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.

vcgaomsft_1-1652850625930.png

In the excel analysis they are also all sorted numerically.

vcgaomsft_3-1652851017225.png

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

vcgaomsft_0-1652782442219.png

vcgaomsft_1-1652782756242.png

vcgaomsft_4-1652851307988.png

vcgaomsft_5-1652851603491.png

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

@v-cgao-msft 

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.

@v-cgao-msft 

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->
1-Publish.jpg

2-Export.jpg

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.

Thanks,

Ed

roncruiser
Helper V
Helper V

@HenriqueReis 

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?

Thanks!

HenriqueReis
Super User
Super User

Hi, 

 

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

Maybe its reading the data in different format.

 

HenriqueReis_0-1652609581667.png

 

Regionality settings break me down lately.

 

 

Regards!

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors