cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper III
Helper III

Getting wrong value when I convert the column in decimal in Power query?

Hi

 

I am having below column called "Amount" which is in Text type

bourne2000_0-1632721828911.png

 

I am trying to convert this column in decimal number in Power Query and getting below values. It replaces the decimal value 

bourne2000_1-1632721880807.png

 

Again I tried to convert into fixed decimal

 

bourne2000_2-1632721993902.png

 

 

Here the decimal points are not coming correctly.  Can you please advise why this is happening? My data comes from SQL server

 

 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @bourne2000 

Right click in the column header and try using Change Type -> Using Locale

ctul3.png

 

Then select Decimal Number and the locale of the origin of the data

ctul2.png

 

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User
Super User

Hi @bourne2000 

Right click in the column header and try using Change Type -> Using Locale

ctul3.png

 

Then select Decimal Number and the locale of the origin of the data

ctul2.png

 

Regards

Phil

 



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

amitchandak
Super User
Super User

@bourne2000 , seem like because of a regional setting comma, is a decimal seprarator.

 

Not the full stop/dot (.)

 

So replace . with , first and then try to convert

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
PhilipTreacy
Super User
Super User

Hi @bourne2000 

How are you doing the conversion?  By clicking on the ABC icon in the column header and choosing Decimal Number?

If you do this then you should get a step with the code

= Table.TransformColumnTypes(Source,{{"Amount", type number}})

Are you seeing this?

Can you share your data file?

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


@PhilipTreacy Thanks. Yes I am doing the same

 

This is what I am getting, I removed the first row

 

bourne2000_0-1632723786432.png

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.