Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Bug: Table.ReplaceValue kills all column types

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...

Status: Accepted
Comments
ImkeF
Super User

Thanks @asarraf21 ,

great stuff!

Anonymous
Not applicable

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
New Member

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

Any updates?

 

Best regards,

Robert

 

pvtzzzz
New Member

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

Brian_Hayes
New Member

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
Super User

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.