Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

Then replace the commas with dots.

 

Then replace the x's with commas.

Jimmy801
Community Champion
Community Champion

Hello @Anonymous ,

 

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

Mariusz
Community Champion
Community Champion

Hi @Anonymous 

 

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.

 

Anonymous
Not applicable

Where would I slot that into the existing code? 

 

Query.PNG

Hello @Anonymous ,

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors