Detect type of column in Power Query Formula Language

Question

I've got some query tables generated from imported data. All the data comes in as type 'Any', and I'm trying to automatically detect the type of the data in each column.

Some of the queries generate tables with columns based on the in-coming data - I don't know what the columns are going to be until the query runs and sets up the table (data comes from an Azure blob). As I will have quite a few tables to maintain (which columns can change with any data refresh - possibly new columns being added), it would be unmanageable to go through all of them each time and press 'Detect Data Type' on the columns.

So I'm trying to figure out how I can do a 'Detect Data Type' in the query formula language to attach to the end of the query that generates the table columns. I've tried grabbing the first entry in a column and do Value.Type(column{0}), however this seems to come out as 'Text' for a column which has integers in it. Pressing 'Detect Data Type' does however correctly identifies the type as 'Whole Number'.

Does anyone know how to detect a column's entry types?

P.S. I'm not too worried about a column possibly holding values of different data types

imkef · Answer

Table.Schema(YourTable) will deliver an analysis of all your table columns with a type-column as well.

marcelbeug · Answer

You can select the applicable columns and choose "Detect Data Type" on the Transform tab.&nbsp;&nbsp;

stevedep · Answer

Hi&nbsp;ImkeF&nbsp;,&nbsp;jPinhao&nbsp;,
&nbsp;
See below for an implementation of Imke's suggestion.&nbsp;
&nbsp;
= (tbl as table) as table  =&gt;
let
    fn = (tbl as table, col as text, numberofrecords as number, marginforerror as number) as type =&gt;
    let
        LijstmetValues = List.FirstN( Table.Column(tbl, col),numberofrecords), 
        Env = Record.Combine({[L=LijstmetValues],[DTF = #shared[DateTime.From]], [DF = #shared[Date.From]], [NF = #shared[Number.From]], [TF = #shared[Text.From]], [LT = #shared[List.Transform]], [LS = #shared[List.Select]], [LC = #shared[List.Count]]   }),
        NumberOfErrors = List.Transform({"DTF", "DF", "NF", "TF"}, each Expression.Evaluate("
                    LC(LS(
                        LT(L, each try  " &amp; _ &amp; "(_) otherwise ""Error""), each _ = ""Error""))", Env)),
        CheckWithinMargin = List.Transform(NumberOfErrors, each _ &lt;= numberofrecords * marginforerror),
        typenr = List.PositionOf(CheckWithinMargin, true),

FirstTypeWithinMargin =  {"datetime", "date", "number", "text"}{typenr},
        CheckType = if List.Distinct(LijstmetValues){0} = null then 4  else FirstTypeWithinMargin,
        result = Record.Field([number = type number,date = type date,datetime = type datetime,text = type text, any = type any],CheckType)
    in result,
    Columnsto2Type = Table.TransformColumnTypes(tbl, List.Transform(Table.ColumnNames(tbl), each {_,  fn(tbl,_, 1000, 0.05)}))    
in
    Columnsto2Type
&nbsp;
&nbsp;
Hope it works for you.
Kind regards, Steve.&nbsp;
&nbsp;
&nbsp;
&nbsp;

jpinhao · Answer

Thanks for the reply ImkeF.I had a look and&nbsp;this and tried it out. As I suspected this returns the current type of the columns- in this case Any.Type.&nbsp;What I am looking for is to be able to deduce a&nbsp;column's type based on it's&nbsp;content, in the same way that pressing 'Detect Data Type' in the ribbon does. I can't imagine this being impossible , but I'm having a hard time figuring out how to. The closest thing I thought of would be something like getting the values of a column in a list, and then&nbsp;&nbsp;List.MatchesAll(column, each Values.Is(_, 'a type'))&nbsp;and do that for a hierarchy of types until I find one that matches.This would force me to have a hierarchy of types to check (do I have dates first? numbers? floats?), which seems incredibly easy to break, and it also seems quite wasteful.Isn't there another way?

imkef · Answer

Nothing I can think of but some simple stupid brute force: Convert a column to number and check if there are errors in there: then it's text. Convert to date and check if it is before ? and after y and no errors: then it's probably number...

Forum Discussion

Detect type of column in Power Query Formula Language

9 Replies

Related content

If formula

formula

DAX Formula Help

Detected Type Mismatches

M Language Transformations