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")
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.
@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_typesdefined 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.