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

[v-qiuyu-msft]

Hi @ImkeF,

I have reported this issue internally: CRI 43913565. Will update here if I get any response.

Best Regards,
Qiuyun Yu

[v-qiuyu-msft]

Hi @ImkeF,

Please see below response from PG:

We've tracked this issue internally. Note that there's a significant constraint on any change we make which is that the new column type must be inferable from the arguments. That is to say that the following query will currently return "type any" and can be made to return "type text":

let
    table = #table(type table [A=text], {{"a"}}),
    replaced = Table.ReplaceValue(table, each [A], "ab", Replacer.ReplaceValue, {"A"})
in
    Type.TableColumn(Value.Type(replaced), "A")

However, this query will always return "type any", because we can't infer the type of the replaced value:

let
    table = #table(type table [A=text], {{"a"}}),
    replaced = Table.ReplaceValue(table, each [A], each [A] & "b", Replacer.ReplaceValue, {"A"})
in
    Type.TableColumn(Value.Type(replaced), "A")

To make this work as desired, it will be necessary to rewrite that query as
   
let
    table = #table(type table [A=text], {{"a"}}),
    replaced = Table.ReplaceValue(table, each [A], (row) as text => row[A] & "b", Replacer.ReplaceValue, {"A"})
in
    Type.TableColumn(Value.Type(replaced), "A")

(once the change has been implemented).

Best Regards,
Qiuyun Yu

[ImkeF]

Thank you @v-qiuyu-msft and team for this excellent explanation! Very much appreciated.

So currently this doesn't work in PowerBI (still returns any), while working correctly in Excel.

It would be great if you could let us know once this change will/has been implemented.

Thx again, Imke

[ImkeF]

Correction here: It doesn't work in Excel as well.

[ImkeF]

I was a bit too quick with my positive feedback of this suggested solution:

Please check that the larger underlying problem will also be solved: Currently, not only the columns whose values are replaced are transformed to "any", but all other column in the table as well. So while I can see that a manual and explicit re-ascrible of a type for columns who are affected by the replacement will work, it doesn't adress the basic problem at all. So you might want to check that the planned changes will actually cover that as well.

please check out this code:

= let table = #table(type table [A=text, B=text], {{"a", "b"}}), replaced = Table.TransformColumnTypes(Table.ReplaceValue(table, each [A], each [A] & "b", Replacer.ReplaceText, {"A"}), {{"A", type text}}) in [TypeA= Type.TableColumn(Value.Type(replaced), "A"), TypeB= Type.TableColumn(Value.Type(replaced), "B" )]

It solves the type-conversion of the replaced value already in the current setup, but illustrates the problem with column b: It has been a text-column, but the "replace"-step has converted it to type any.

[v-qiuyu-msft]

Hi @ImkeF,

I have delivered your findings to PG team, if there is any update, I will let you know.

Best Regards,

QiuyunYu

[v-qiuyu-msft]

Hi @ImkeF,

PG already added your suggestion to the bug report internally.

Best Regards,
QiuyunYu

[ImkeF]

Thank you!

[asarraf21]

@ImkeFI got into the same problem and I solved it by using this function that I found here:

Say you have a function called revert_column_types defined as below:

(baseTable as table, processedTable as table) =>
let
    baseTableType = Value.Type(baseTable),
    CommonColumnNames = List.Intersect({Table.ColumnNames(baseTable), Table.ColumnNames(processedTable)}),
    processedTableNewColumnTypes = 
        List.Transform(
            CommonColumnNames, 
            each {_} & {Type.TableColumn(baseTableType, _)}
        ),
    appliedColumnTypes = Table.TransformColumnTypes(processedTable, processedTableNewColumnTypes)
in
    appliedColumnTypes

Now in your code you can do this:

// let's assume old_table is the original table with all the right column types
// now you do some crazy things like new_table = Table.ReplaceValue(old_table, ...)
// all you need to do after is to call that function above
result = revert_column_types(old_table, new_table)

I arrived at this solution after a day of trial/error and that finally worked for me. Hope it helps, too.