I have a column with prices that I download from a business system, and depending on the country, the numbers have different formats, such as:
1.260,14 -> this is seen as text by Excel
1,280.14 -> this is seen as text by Excel
1280.14 -> this is the only correct one, seen as number
I want Power Query to transform everything to numbers, which in this case means that all 3 numbers should be: "1280.14"
1) Split your column at the decimal-position: Split column by number of characters: 2 - Once as far right as possible
2) First column: Replace "." by "" (nothing)
3) First column: Replace "," by "" (nothing)
4) Merge both columns with "." as delimiter and change to decimal format
If you know which documents come from which country, you can also take the locale into account when using Table.TransformColumnTypes. You can right-click on the column and choose Change Type | Using Locale... . This will generate something like Table.TransformColumnTypes(Step, {{"Column Name", Currency.Type}}, "locale name").
Here's a Power Query function I just made to cast the mixed format texts as numbers. It considers the last occurance of dot or comma as the decimal separator, as long as that character occures only once in the text.
To use this function, go to your "Power Query" ribbon tab, click "From Other Sources" > "Blank Query". Then go to "Advanced Editor" and copy-paste script below to the editor and save. You can then go back to your main query and click "Add Column" > "Add Custom Column". The formula after "=" would be: toNumber([column name]).
let
toNumber = (text) =>
let
//remove characters that occur more than once as they can't be decimal separators
text1 = if List.Count(Text.PositionOf(text, ",", Occurrence.All)) > 1
then Text.Replace(text, ",", "") else text
, text2 = if List.Count(Text.PositionOf(text1, ".", Occurrence.All)) > 1
then Text.Replace(text1, ".", "") else text1
//if there are still more than one potential decimal separator, remove the kind that occurs first
//let's assume the last one is the actual decimal separator
, text3 = if List.Count(Text.PositionOfAny(text2, {",","."}, Occurrence.All)) > 1
then Text.Replace(text2, Text.At(text2, Text.PositionOfAny(text2, {",","."}, Occurrence.First)), "")
else text2
//cast as number (try different decimal separators)
, number =
try Number.ToText(Number.From(Text.Replace(text3,",",".")))
otherwise Number.ToText(Number.From(Text.Replace(text3,".",",")))
in
number
in
toNumber
You can determine the decimal by:
Text.Middle(Number.ToText(1 / 2), 1, 1)
Number.ToText(1 / 2) is a string "0.5" or "0,5" depending on whatever unknown locale you use. Text.Middle will return the second character of that string.
