Power Query - The Excel Connector "Corrupts" Numbers (v2)
HTML-код
- Опубликовано: 9 фев 2025
- **** Check out the TOP COMMENT for THE BEST SOLUTION (way better than mine). ****
Learn why Excel has the potential to "corrupt" numbers when used as text. This examines the storage tactics of fractional values in newer versus older Excel file formats.
This is a re-upload: There was an issue with the audio. It's FIXED now.
01:58 How Excel Stores Numbers
02:47 Excel File Format Differences
03:17 Opening Office Files as ZIP Files
03:43 Extracting ZIP File Data
04:58 Distilling the Behavioral Issue
05:17 Reading the Excel Data Properly (semi-hack workaround)
06:57 Summary of Problem & Solution
As I commented in the original video, a simple way to fix this is in Power Query, instead of setting the column type to 'type number', set the type to 'Double.Type'.
WOW! You just successfully rendered my solution worthless!! Just kidding; that's wonderful. This is exactly why I made this video, so a smart person would give us the answer. You get the Gold Star for the week. Thank you, Thank you, thank you.
That is really an interesting and valuable solution to this issue.But nowhere we can find that there is data type of 'Double.Type' in microsoft documentation for power query. Thanks for sharing this great tip 👍.
what's double type?
@ After you set the initial data type (to pretty much anything else), you have to go into the M code and replace the existing data type with “Double.Type”. It’s not a selectable option in the normal list of data types.
Technical info good to know, always.
Changing xls extension to zip was a great tip. Thank you. 👍
That is a cool trick. I'm glad you liked it.
Great video Bryon, I would never have thought of this solution
Thanks. It took a LOT of Googling (with not much return) and a lot of experimenting to figure this out. It may not be the most elegant solution, but it works.
Thanks for the video. Sometimes Text.From gives a different result than changing type. Worth to try.
Thanks for your suggestion. I tried but it does not work for this case.
Yeah, I tried that , too. It's always good to eliminate what doesn't work so we can find what does. Thanks for watching.
Awesome. (BTW I would contribute $ $ to you for this insight)
@@weav8060 And I would gladly take it! 🤗
Would this cause issues with numbers when filtering greater than, less than and equal to? Particularly with currency formatting.
I don't see why that would avoid the issue. The "problem" occurs long before any analytics occur. Good question.
Would the problem still exist if the model numbers were already formatted as text in the original. xlsx file instead of general or number and then try to convert to text in PQ?
@@ianl1052 Nope, I tried that. It didn’t seem to make a difference. Good thought, though.
@@bcti-bcti
Just a thought. Back to the drawing board.
Audio was fine for me the first time. Is the problem discussed in the video only an Excel thing? What happens if you have a folder of files? Do you need to save ALL the files as .xls?
I would assume so, as each Excel file in a folder would be examined at that "deeper" level. Luckily, it's not a problem that would pop up for most users. Thanks for watching.
Interesting video, I recognize maybe a similar issue: I wanted to mailmerge (with outlook) my marks to students, a mark of 6,5 was forwarded as 6.50000000001 or something, I solved this issue then by using TEXT(number;"0,0")
Yep, you discovered the issue, my friend. Hope this helps you avoid it in future projects.
I have also encountered these Binary Floating-Point notation issues sometimes in Goal Seek tools or the Solver plugin. Has anyone else encountered this? Thanks for the tutorial.
I wasn't sure how many users would find this valuable, but I like understanding Excel at the deeper levels. It's interesting if nothing else.
@@bcti-bcti Don't worry, I've learned a lot from all your tutorials.
thanks as always great video
@@ahmedshalaby9343 Thank you!
What's weird is that the extra digits get added in the XML even if the cell format is text.
A workaround I found if you can put it in your source:
=TEXT(D5,"0."&REPT("0",LEN(TEXTAFTER(D5,"."))))
When this comes it it doesn't have the extra digits, and when you change (if you need to), it converts without adding the digits also.
Yeah, that could work, but if you're reading from multiple Excel files, and new files are added frequently, that's work that users are not going to want to do. It could work for other scenarios, though. Good to see you thinking.
Nice explanation. But I still don't understand WHY Excel can't store certain numbers accurately. Why does it add those extra places?
@@iankr That, my friend, is a question for the designers of Excel. I have no earthly idea. I don’t see why it can’t be done, but I’m sure there’s an explanation out there somewhere.
Some decimal numbers that are rational in base 10, are irrational in base 2 and vice-versa. So values in Excel end up doing some funny things.
I first came across this issue when testing for equality on two numbers that were the result of the same set of calculations but that were done in a different order. After some google about irrational numbers and decimal to binary conversion it made sense.
@ We’re not reaching an altitude that my brain requires supplemental oxygen.
Did I miss why this is a problem that needs a solution? What is at risk if the numbers being ordinal are going to be rounded anyways?
@@Faith4AllofLife Yes, you missed the issue. The numbers for this user are to be treated as text because they are used as identifiers (i.e., model numbers). The numbers are not to be rounded or styled.