Power Query -> Transform Comma to Period and Obtain Decimal Numbers

6
votes

I'm working within Excel Query Editor (Power Query) and I have a table with many columns.

Some of the numbers in those columns have a period (".") and they are properly recognized as decimal numbers, but some of them have a comma (",") and they are seen as text.

I'm trying to use Query Editor's Replace Value function in order to replicate Excel's Find and Replace function (CTRL+H) and simply change the "," with "." but without any luck so far.

Do you know a fix to this, please? Thank you very much.

2
excelpowerquerym
Is it refusing to replace the commas, or refusing to recognize as a number after replacing? If the latter, is it not possible to just set the type of the column back to "general" or "number"? – nwhaught
Managed to solve it in 3 steps: 1. Data Type: Text 2. Replace Values: "," with "." 3. Data Type: Decimal Number Thank you for the suggestion, nwhaught. – jb007

2 Answers

9
votes

Managed to solve it in 3 steps: 1. Data Type: Text 2. Replace Values: "," with "." 3. Data Type: Decimal Number

2
votes

Use transform with locale settings:

= Table.TransformColumnTypes(#"Promoted Headers", {{"title", type number}}, "en-US")

Source: https://eriksvensen.wordpress.com/2018/07/02/powerquery-how-to-handle-different-decimal-separator-when-importing-csv-files/