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

[WanderingBI]

I realize this is an older post, but want to add some information that might be useful to other users who face this issue:

In the case of importing from Excel files (i.e. using the Excel.Workbook() function), Power Query will detect the column data types when evaluating the function and set the column types automatically based on analysing the first 1000 rows of data.

They then often get lost in the "Expand" step where the output is columns of type any.

Before you expand, you can get the column types by using the Table.Schema() command and save them. That information can then be used to "paste" the correct column types back on the columns after they are expanded.

This way you do not need the hard coded step that is created when clicking on "Detect Data Type".

[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