Inaccurate Decimal Numbers

[StephenK]

Can someone explain this to me?

I have an excel table that i have imported into power query. 

I have a column [Current Score] with datatype decimal number. (See green highlighted column and note the values 1.83333333 and especially the 2.55) 

I apply a split column by first four characters to truncate to two decimals without rounding. Now note the 1.83 and the 2.55 which is now 2.54. 

To make it even more confusing, the value in the excel table is 2.55 not 2.549999999999998. Does anyone know why Power Query would be doing this? It creates a serious data integrity problem.

[Jimmy801 · Accepted solution]

Hello @StephenK 

I just can confirm you that it's like that. It's really weird. Try to open a ticket.

However there are some workarounds. Transform you raw data (without any changed type) with the round-function and put 10.

or you transform it and multiply your data with 1

In the other workarounds you have to change your Excel. You can save your workbook as CSV-file or format your content as text.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[StephenK]

@Jimmy801 thanks for the workaround! Seemed to do the trick. Really appreciate it!

Still kinda frustrated though--would like to hear from anyone on the Power BI dev team about this. It's concerning to me that queries become innacurate as a result of this bug. One would think that there would not be any data integrity issues between Excel and Power BI given they are both Microsoft products.

If we can't count on the platform to at least maintain accurate queries why would we use it...

[Jimmy801]

Hello @StephenK 

I completely agree. I can try to forward this somehow. However I marked my workaround as solution.

BR

Jimmy

[HotChilli]

Can you show us the Advanced editor code (including the split column) please?