Auto "Detect Data Type" for newly added columns

[Tegzi]

Hello,

maybe somebody already found a solution for a small problem. I love when my queries are "bullet proof" as much as possible, i.e. they don't break down when something changes in the raw data source.

The most typical last step for me is assigning Data Type (type text, type number, Int64.Type, etc.) to the columns after I did all the necessary transformation. The problem is that PQ hardcodes the columns. So, if in the future a new column is added to the raw data, then this last step - Detect Data Type - will NOT be done, unless I go back to the query and modify the last step.

For example, this is my last step in a query:

= Table.TransformColumnTypes(#"Replaced Value",{{"Sales Document", Int64.Type}, {"Customer Name", type text}, {"Article Description", type text}}). If tomorrow I add a new column - e.g. Quantity - to the raw data, then the Detect Data Type would not recognize this as Int64.Type (full number), because the above step is hardcoded.

I am not sure there is a function like "Table.DetectColumnType" or something that would enable such, so that I could avoid the hardcoding piece of the story.

Hope this is clear and thanks for your reading.

Tegzi

[ppm1 · Accepted solution]

This is not easy, but this article describes one way to do it. It won't be performant at large scale though.

Automatic Detection of Column Types in #PowerQuery - DataChant

Pat

[Tegzi]

Thank You Pat,

indeed a good source and will have to be careful when and how to use it. Honestly, I just wanted to see there are options on the table to learn new ways 🙂

Thanks again,

Tegzi