7

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.

jb007
  • 243
  • 1
  • 5
  • 17
  • 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 Jan 11 '16 at 05:23
  • 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 Jan 11 '16 at 14:30

2 Answers2

10

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

jb007
  • 243
  • 1
  • 5
  • 17
  • Doing the 3 steps is less direct than using a 3rd parameter with locale setting (as the answer of Dmitry). Not only that. If you wish to apply automatic type transformation to all columns the replace value step will mess up some genuine text column. Using the locale setting in TransformColumnTypes() function you can select all column and don't have to worry to look at the specific numeric ones. – A.Sommerh May 24 '22 at 20:01
5

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/

Dmitry
  • 470
  • 6
  • 9
  • Great! That is the way! In other words: To put a third parameter ("en-US") to the TransformColumnsTypes() function. Power Query on importing just include the first 2 parameters to all column needed (and the menu "transform column type" also). So we can continue using it a usual, but just doing the small adjust at the end of the formula (Adding "en-US")! – A.Sommerh May 24 '22 at 19:56
  • I just had the same issue with a large data set (CSV files) that was created using a computer set with European locale settings. In order for my Excel power query to read the CSV files correctly, I needed to add "it-IT", as this is the format the CSV files were created with. – hamslice Aug 21 '23 at 12:41