Hi!
I'm having an issue when loading a .csv file to excel, going through power query.
The values in the original file have 4 decimal points which I need to preserve for the precision of the output I'm trying to obtain in excel, but when I run it through power query it only shows up to 2 decimal points, the other 2 seem to be lost and I don't know how to go about fixing this.
I set the column with the data I need to number (it initially shows as text) but that's about all I've been able to do.
I thought that maybe if I formatted the values to 4 decimal places in excel they'd show the missing information, but it didn't work.
Could you point me in the right direction? I could send you a sample file if necessary.
Thanks in advance!
Regards
Eduardo
Hi Eduardo,
You should set the data type to Decimal Number. If you're still stuck, please provide an extract of your data in the csv file and let us know what locale your PC uses so we can replicate any regional settings.
Mynda
Hi Mynda
Thank you for getting back to me! I've tried changing it to decimal number but it still didn't work, I'm completely stuck. I'm also very new to power query, I've mostly just been absorbing all I can from Youtube!
I'm attaching a csv file for you to possibly simulate. Regional settings here are for Portugal. I have the same file in excel data only format, I don't know if that helps for comparison?
https://drive.google.com/drive/folders/16mArBTbs91J1Z5fmT8F7VWlMgndyiDdR?usp=sharing
Any help would be great, thank you so much!
Eduardo
Hi Eduardo,
Thanks for sharing your files. The most I'm seeing is 3 decimal places. In cells D20 and E20 of your Artigos.pdf.xls file there are the following numbers:
D20: 6768.99999999963
E20: 426493.3395
But those values in the Extratos EMB 01-2021.csv file (when opened in Notepad, so with no editing) are 6 769,000 and 426.493,34 respectively. So it is in converting the data to the csv file that the values are being rounded, as opposed to a problem with Power Query.
The file name of the .xls file implies that this data came from a PDF originally.
Mynda
Hi Eduardo,
If you try to import data from Artigos.pdf.xls into power query, it will not read the cell value, it will actually import the displayed formatted value.
This problem seems to be generated by the PQ connector/driver used for xls files, nothing we can do about it, we can only raise a ticket to PQ development team.
All you can do is to convert the Artigos.pdf.xls to .xlsx format (save as new excel format .xlsx)
When you import the .xlsx version, will work as expected.
Hi! Thank you so much for your replies! I've had to call the software house here in Portugal to see if there's anything they could do about the rounded csv file but I'm still waiting on that.rnThe other file, the .xls has all the information I need as well, as you mentioned Catalin, I'm just stuck on how to clean it up in power query so it looks like the new file I added in the folder (orange pulp) which I did manually in excel.rnSee, in the original file each product has a row identifying it and then below that are the movements for each of the products. How could i get power query to bring the product name to a column beside each of the movements? And how would it know when to change the name? I'm sorry for asking what are probably stupid questions, I'm just very very new to power query and trying to learn on my own has been quite the task!rnThank you once again!rn rn rn
