Subtracting two decimal numbers gives 100x the value

[maflingo]

Hi,

I have an issue where I'm creating a new column in M by subtracting two columns containgin decimal values. However, when the numbers have decimals other than ,00 it returns 100x the value, so for instance 652,96-676 becomes -2304 instead of -23,04. I've tried converting the columns to decimals a few different ways, but the error persists. This is the relevant snippet in M, would be grateful if anyone has an idea, thanks!

   #"Changed to num" = Table.TransformColumnTypes(
    #"Expanded FactValue (2)", 
    {{"cdmValue", type number}, {"FactValue (2).cdmValue", type number}} ),
    
    // Takes the difference between this and previous month's values if 01, 02, 08 or 12, else the value
    #"Added Value-col" = Table.AddColumn(#"Changed to num", "ValueMonthly",
            each if Text.Contains([idMetric], "IF01") 
             or Text.Contains([idMetric], "IF02") 
             or Text.Contains([idMetric], "IF10") 
             or Text.Contains([idMetric], "IF12") 
            then 
                if [#"FactValue (2).cdmValue"] <> null 
                then [cdmValue] - [#"FactValue (2).cdmValue"]
                else [cdmValue]
            else [cdmValue]
            ),

    #"ValueMonthly to num" = Table.TransformColumnTypes(#"Added Value-col",{{"ValueMonthly", type number}})

[Anonymous]

I would specify Currency.Type instead of type number.

--Nate

[dufoq3]

Hi @maflingo, in my opinion the issue is with locale. Try to use ChangedType step with locale

let
    Source = Table.FromList({{652.76, 676}}, (x)=> x),
    ChangedTypeGB = Table.TransformColumnTypes(Source,{{"Column1", type number}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(ChangedTypeGB, "Custom", each [Column1] - [Column2])
in
    #"Added Custom"

[maflingo · Accepted solution]

Thank you both for the suggested solutions. The issue was fixed somehow while I was fixing something else. No idea what it was in the end..