Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

DSTUART

Excel Data - Issue converting numeric to text

Submitted by on ‎08-22-2017 05:33 AM

I am trying to import a mixed (number and text) column from Excel into PowerBI, due to the start of the column all being numbers PowerBI has chosen the column type as numeric.  However when I change type to text the values 1.1 and 2.2 are being incorrectly converted to 1.1000000000000001 and 2.2000000000000002 respectively.

 

I have tested with a small sample set of data and am able to easily replicate see below:

 

ExclConv.PNG

PowerBI Convert Test.PNG

Status: Accepted
See more ideas labeled with:
5 Comments (5 New)
Comments
v-haibl-msft
Employee
Employee
‎08-23-2017 12:27 AM

@DSTUART

 

I can repro the same issue as you. I’ve reported it internally to Power BI Team: CRI 45625491
I’ll post here once I get any update about it.

 

Best Regards,
Herbert

Vicky_Song
Impactful Individual
Impactful Individual
‎08-23-2017 12:32 AM
Status changed to: Accepted
 
v-haibl-msft
Employee
Employee
‎08-23-2017 07:01 PM

@DSTUART

 

I've got response from the Product Team.

 

Instead of changing the type again, the customer should delete the previous type conversion to number by editing the automatically-generated formula that changes the type the first time.

When you enter a "1.1" into a cell, Excel actually stores it as follows: <row r="2" x14ac:dyDescent="0.25" spans="1:1"><c r="A2"><v>1.1000000000000001</v></c></row>

So you can see that we're accurately showing the value stored by Excel even though it differs from the value displayed by Excel.

 

As a workaround, you can split column as following screenshot.

 

Excel Data - Issue converting numeric to text_1.jpg 

 

Best Regards,
Herbert

DSTUART
Regular Visitor
‎08-24-2017 01:36 AM

Thanks for the feedback, but this is a workaround that can be achieved outside of PowerBI. But it would be better with bug solution.

amirshiloh
Advocate I
Advocate I
‎11-16-2021 04:27 AM

bug still exists,

first partial solution would be to add a step at the Power Query data model as follows:

it won't solve an issue as convert 1.1 and 1.10 - these two values will transfered to value of 1.1 😐

= Table.TransformColumns(#"<MyLast step name>", {{"MyValue", each try Text.From(Number.Round(Number.From(_),2)) otherwise Text.From(_), type text}})

second partial solution (need to edit the Excel at the source) - add ' at the orignal excel before the value and it will fix it when importing the data to power bi -when transfer the value type to text it will dispalyed correctly

Idea Statuses