Data type changed to ANY after Table.ReplaceValue

[Yuppiedad]

Hi,

It is quite strange that data type of all columns changed to "ANY" after executing Table.Replace,Value. No problem if I just replace null with other word such as "Hello". The problem shows when I replace null with data from other column. In my case here is the data from [Product Line.1].

Is it a bug? Can it be fixed?

#"Replaced Value3" = Table.ReplaceValue(#"Reordered Columns",null,each [Product Line.1],Replacer.ReplaceValue,{"Product Line"}),

Thanks,

WaiChung

 

[Rudi]

You could try using the Value.ReplaceType function.

#"Get Column Type" = Value.ReplaceType(#"<Previous Step Name>", Value.Type(#"<Previous Step Containing Preferred Types>"))

Sample code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order ID", type text}, {"Order Date", type datetime}, {"Site", type text}, {"SP Name", type text}, {"Category Name", type text}, {"Ship Via", type text}, {"Quantity", Int64.Type}, {"Order Total", type number}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",null,each [SP Name],Replacer.ReplaceText,{"Ship Via"}),
    #"Get Column Type" = Value.ReplaceType(#"Replaced Value", Value.Type(#"Changed Type"))
in
    #"Get Column Type"

Note: The step only reapplies the column types, it does not actually change the content of the column to a new data type.

 

[Yuppiedad]

Hi Rudi,

I might not clearly describe the phenomenon. I have changed all columns to correct data type at very beginning. I mean data types of ALL columns , not the only one I am working on, being changed to "ANY" after the Table.ReplaceValue.

Regards,

WaiChung

 

[Rudi]

The line of code I refer to does change all the columns types for the whole table, not just one column.

Did you try the code in your query?

What this line does is it collects all the column types from a step where the columns are correctly defined, then it applies those column definitions to all the columns of the table in the current step.

 

[Yuppiedad]

Hi Rudi,

It is sure that your code works to recover data types on all columns.

I just wonder why all data types on all columns changed after executing Table.ReplaceValue.

Regards,

WaiChung

 

[Rudi]

I am not sure why PQ does that? It's either a glitch or these is some internal reason for the columns becoming type "any" after using the "each" statement.

Maybe someone else will provide a more informed comment on your question.

Cheers

 

[MarcelBeug]

Not really more informed, but I have a video basically confirming Rudi's solution.

I actually published 3 videos in a short play list about function Value.Type, with 3 situations I'm aware of, where column types are compromised:
This one (Table.ReplaceValue), Group By (using operation "All Rows") and reading files from a folder.

 

[Rudi]

You have some good videos Marcel.
An excellent resource. :clap2:

 

[MarcelBeug]

Thanks, Rudi.