Power Query: Replace values without converting integers / whole numbers to floating point / decimal numbers?

Question

Minimum Working Example

Power Query M

let
    Source = #table({"col"},{{1},{null}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,Int64.From(0),Replacer.ReplaceValue,{"col"})
in
    #"Replaced Value"

In #"Changed Type", the column is of type "Whole Number". In #"Replaced Value", the column is of type "Decimal Number", even when using a newValue of Int64.From(0) instead of just 0.

Why is the data type of the column changing and how can I prevent it?

Answer 1

This is an old bug documented here. Even with a custom replace function, you still can't fix it and can only convert to a primitive like number.

let
    Source = #table({"col"},{{1},{null}}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"col", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",each [col],0,(x,y,z) as number => if y = null then z else x, {"col"})
in
    #"Replaced Value"