cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jPinhao
Helper II
Helper II

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

8 REPLIES 8
sminonese
Frequent Visitor

This should be an idea, as the current implementation requires you to specify the column names. Adding a new column to a table means you need to update the "Change data type" step manually. There should be a "detect data type on all columns, exluding some" step so even automatically added columns will automatically get an auto-detected data type.

 

Alternatively, you could think about unpivoting your data so you sort the data according to data types. One column for quantitative values, one column for text values, and one column for the "Attribute" name. Then it's only a matter of adding rows to the table, rather than adding columns.

 

 

@sminonese @jPinhao were you guys able to work this out. Same issue i have come across where i have to detect the data type of new columns and convert accordingly.

MarcelBeug
Community Champion
Community Champion

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

 

 

Specializing in Power Query Formula Language (M)

I have to detect dynamically, when new columns are picked up while importing the csv file. I wish if it was that simple. 

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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?

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

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

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. 

 

 

 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors