cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AaronCoughlan Frequent Visitor
Frequent Visitor

Data Cleaning: Converting from European to US/UK Number format

How do I convert from a ##.###,## number format to a ##,###.## number format?

 

There is no room to edit the data before it comes into Power Query, so needs to be changed within the query.

 

Current format is set to text as it cannot read the current character format as Decimals.

 

Capture.PNG

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
AaronCoughlan Frequent Visitor
Frequent Visitor

Re: Data Cleaning: Converting from European to US/UK Number format

The solution was to replace the dots with x's.

 

Then replace the commas with dots.

 

Then replace the x's with commas.

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Data Cleaning: Converting from European to US/UK Number format

Hi @AaronCoughlan 

 

You can replace values like below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQzN7TQszQ30TExVIqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",".","#",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",",",".",Replacer.ReplaceText,{"Column1"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","#",",",Replacer.ReplaceText,{"Column1"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value2",{{"Column1", type number}})
in
    #"Changed Type1"

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

 

Jimmy801 New Contributor
New Contributor

Re: Data Cleaning: Converting from European to US/UK Number format

Hello @AaronCoughlan ,

 

you can apply a Table.TransformColumns like this

Table.TransformColumns(Quelle, {{"decimals", each Number.From(_, "de-de")}})

 

If this post helps or solves your problem, please mark it as solution.
Kudos are nice to - thanks
Have fun

Jimmy

AaronCoughlan Frequent Visitor
Frequent Visitor

Re: Data Cleaning: Converting from European to US/UK Number format

Where would I slot that into the existing code? 

 

Query.PNG

AaronCoughlan Frequent Visitor
Frequent Visitor

Re: Data Cleaning: Converting from European to US/UK Number format

The solution was to replace the dots with x's.

 

Then replace the commas with dots.

 

Then replace the x's with commas.

View solution in original post

Jimmy801 New Contributor
New Contributor

Re: Data Cleaning: Converting from European to US/UK Number format

Hello @AaronCoughlan ,

somewhere in between.. meaning createing a empty line, create a new variable like Transform  =,

past my code and connect it to the steps and after

 

BR

 

Jimmy

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)