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!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!