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?
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.
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.
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!
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.
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.
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.
Re-accessing that same Dataset in the Power BI service using Excel and the datatypes are different.
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?
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.
Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.
Watch the playback of Session 26 with Ted Pattison!
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.
Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.