Forum Discussion

jPinhao's avatar
jPinhao
Advocate II
10 years ago

Detect type of column in Power Query Formula Language

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 gener...
Need Help
ImkeF's avatar
ImkeF
Community Champion
10 years ago

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

  • jPinhao's avatar
    jPinhao
    Advocate II
    10 years ago

    Thanks for the reply ImkeF.

    I had a look and this and tried it out. As I suspected this returns the current type of the columns- in this case Any.Type.

     

    What I am looking for is to be able to deduce a column's type based on it's 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 

     

    List.MatchesAll(column, each Values.Is(_, 'a type'))

     

    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's avatar
      ImkeF
      Community Champion
      10 years ago

      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...

       

      • stevedep's avatar
        stevedep
        Memorable Member
        5 years ago

        Hi ImkeF , jPinhao ,

         

        See below for an implementation of Imke's suggestion. 

         

        = (tbl as table) as table  =>
let
    fn = (tbl as table, col as text, numberofrecords as number, marginforerror as number) as type =>
    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  " & _ & "(_) otherwise ""Error""), each _ = ""Error""))", Env)),
        CheckWithinMargin = List.Transform(NumberOfErrors, each _ <= 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

         

         

        Hope it works for you.

        Kind regards, Steve.