Bug: Table.ReplaceValue kills all column types

[ImkeF]

When used in combination with "each" in the second argument, Table.ReplaceValue kills all existing column types (sets them back to any). Please fix:

https://social.technet.microsoft.com/Forums/en-US/c3f118eb-acb2-4e36-a398-d6c3759e795f/tablereplacev...

[ImkeF]

Thanks @asarraf21 ,

great stuff!

[Anonymous]

The same problem happens whenever I create a new column with each as second argument.

This is particularly problematic in Direct Query scenarios where the operation of transforming the column back to the original data type is not supported. The solution, so far, doesn't help me at all.

[RFLamping]

It is five year later since the issue was reaised and the problems is still not solved. (Excel).

Any updates?

Best regards,

Robert

[pvtzzzz]

Just ran into this today when replacing values in a single column (Excel)!  

[Brian_Hayes]

The issue seems to be resulting from the return type on the replacer function.  Replacer.ReplaceValue returns type any.  I found that providing a custom replacer function to be a workable solution for many, but not all, cases. 

(originalValue, oldValue, newValue) as number => newValue

A larger example:

let
    Source = Table.FromRecords(
        {[FullName = null, LastName ="Lovelace", FirstName = "Ada", FullNameLength = null]},
        type table[FullName = Text.Type, LastName = Text.Type, FirstName = Text.Type, FullNameLength = Int32.Type]
    ),
    #"Replace FullName" = Table.ReplaceValue(Source, each [FullName], each [FirstName] & " " & [LastName], 
    (originalValue, oldValue, newValue) as text => newValue , {"FullName"}),
    #"Replace FullNameLength" = Table.ReplaceValue(#"Replace FullName", each [FullNameLength], each Text.Length([FullName]), (originalValue, oldValue, newValue) as number => newValue , {"FullNameLength"})
in
    #"Replace FullNameLength"

One case where this will not work perfectly, but maybe sufficiently for you case, is where the the column type is a whole number type such as Int64.Type.  The replacer function can be declared as having a return type that is a primitive type but not a granular type.

[ronrsnfld]

It's now been over six years since this was reported. Still exists in Excel and Power BI desktop.

I wonder if it will ever be fixed.

Unfortunately, as he pointed out, @Brian_Hayes solution works only for primitive types. And I was hoping to preserve a Currency.Type in multiple columns.