Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Please try this solution
P.S. Read question one more time - answer corresponds to question header, but not to question body...
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.
You can select the applicable columns and choose "Detect Data Type" on the Transform tab.
I have to detect dynamically, when new columns are picked up while importing the csv file. I wish if it was that simple.
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
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |