Issue with decimal numbers when importing a CSV file with Power Query
Our take
So in my organization we have a database with CSV files that different departments need from time to time. Lately we have had some problems when importing this data to Excel with Power Query. It seems like the numeric columns, specially the ones with decimal numbers, get all f*** up when importing the files this way. For example (I am in Europe and we use commas for decimals and dots for the thousands):
-Correct number: 155.746,88
-CSV imported: 15.574.688,00
As you can see, for some reason it seems that all the dots and commas are eliminated, then the ,00 decimal is added at the end of the number and then the number is "recalculated" based on these new decimal position. I can't change it "automatically" with power query because the numbers that don't have decimals are affected but don't change in value:
-Correct number: 1.420
-CSV imported number: 1.420,00
Besides, in Power Query the data type of the columns affected appears in "Text", and when I change it to any numerical value (don't matter if it is decimal number, whole number, currency...) it gives me an error.
Any ideas on why this happens and how to fix it?
Thank you and have a good day!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Macro to remove a decimal, but keep the decimal points?The data we use is exported from an accounting software and is exported as a .txt file. The bank will only accept .csv, which we can convert the .txt to. The problem is the dollar amounts export with the decimal (it's about 800 rows of data that needs to be changed), but the bank system won't accept decimals within the file. Its system will place the decimal when it processes the charges. For example: I need to turn 672.35 into 67235 or 100.00 to 10000 (times 800 rows of varying dollar amounts). I understand that working in a .csv is where it gets tricky and I can't find a clear answer myself on how to get this done (or if I can). submitted by /u/Ok_Spot_389 [link] [comments]
- How do I stop Excel from automatically changing decimals displayed.i.e. Let's say I want to type in the following data: 1 1.0 1.00 1.000 Excel will display: 1 1 1 1 But we want the sheet to reflect the precision of the numbers we've entered. I've tried messing with some of the advanced file options without any luck. Is the only way to do this to format all those cells as text or to manually change the decimals displayed for every entry? Edit: Actually, entering as text will fix how the data we enter is displayed but if it is used in any formulas we'll have the same issue with the results. i.e. If we multiply every one of those numbers by 4 then the results column will all display '4' with no decimals and I can't find a way to get it to maintain the precision of the input data. submitted by /u/IceCreamforLunch [link] [comments]
- How to stop Excel from changing decimals to scientific notation?I just got a new work laptop which uses Microsoft Office 365. I regularly pull in stock prices and currency foreign exchange conversion from Yahoo! Finance into CSV, which I then copy and paste values into my Excel spreadsheet. One of the items I pull from Yahoo! Finance is IDRSGD=X, which is basically converting Indonesian Rupiah (IDR) to Singapore Dollars (SGD). Now, say IDR 1 = SGD 0.000077 and it is shown as such in Yahoo! Finance. However, in the CSV, the number automatically becomes 7.7E-5. When I copied and pasted from the CSV to my Excel file, it stayed the same way, ie. 7.7E-5. I know they mean the same thing but it's ugly and it just stands out like a sore thumb against all my other data in my spreadsheet, which are nicely presented in decimals. How can I stop Excel from automatically converting this notation? I went to Options --> Data and unchecked the relevant boxes, but it didn't help. In Excel, even if I Format Cells --> Number to reflect decimals, it does not help either. Somehow the ugly scientific notation overrides the cell formatting. There are many cells in the data set showing scientific notation instead of decimals, and I cannot be going cell by cell. I never faced this problem with my previous Microsoft Office 2019. Can someone please help? submitted by /u/Traditional_Bell7883 [link] [comments]
- Power Query Rounding Issueokay maybe i'm missing something I have a Cell that shows a value of 0.49 I have another cell that divides it by 2 resulting in 0.245 now when I ask Power query to round it to 2 digits its giving me 0.24 not 0.245 why is this? submitted by /u/Falconflyer75 [link] [comments]