• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Power Query: 4 Decimal Places|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Power Query: 4 Decimal Places|Power Query|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumPower QueryPower Query: 4 Decimal Places
sp_PrintTopic sp_TopicIcon
Power Query: 4 Decimal Places
Avatar
Eddies Lima

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2021
sp_UserOfflineSmall Offline
1
February 12, 2021 - 4:29 am
sp_Permalink sp_Print

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

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
February 12, 2021 - 4:21 pm
sp_Permalink sp_Print

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

Avatar
Eddies Lima

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2021
sp_UserOfflineSmall Offline
3
February 13, 2021 - 5:16 am
sp_Permalink sp_Print

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.....sp=sharing

Any help would be great, thank you so much!

Eduardo

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
4
February 13, 2021 - 1:57 pm
sp_Permalink sp_Print

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

Avatar
Catalin Bombea
Iasi, Romania
Admin
Level 10
Forum Posts: 1824
Member Since:
November 8, 2013
sp_UserOnlineSmall Online
5
February 13, 2021 - 5:16 pm
sp_Permalink sp_Print sp_EditHistory

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.

1a.jpgImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage 1a.jpg (122 KB)
Avatar
Eddies Lima

Active Member
Members
Level 0
Forum Posts: 4
Member Since:
February 12, 2021
sp_UserOfflineSmall Offline
6
February 19, 2021 - 12:16 am
sp_Permalink sp_Print

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 

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Catalin Bombea, Bright Asamoah
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 218
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Cathi Giard
Sarah Young
Henry Delgado
Alita Nieuwoudt
KL KOH
Joao Marques
Regi Hampton
Taffie Elliott
Paramita Chakraborty
David du Toit
Forum Stats:
Groups: 3
Forums: 24
Topics: 6358
Posts: 27805

 

Member Stats:
Guest Posters: 49
Members: 32335
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.