The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I get random success/failures with this function. I have a table called Sales with several columns - number, text, Int64.Type, etc.
The following in a new query returns nothing in the list. No error, just nothing.
= Table.ColumnsOfType(Sales,{type text})
The following M code correctly returns the description column:
let Source = #table( type table [Quantity = Int64.Type, Unit Price = Currency.Type, Description = text], { {2,3,"Car"}, {4,5,"Bike"} } ), Custom1 = Table.ColumnsOfType(Source,{type text}) in Custom1
But the following incorrectly returns both the Quantity and Unit Price fields.
let Source = #table( type table [Quantity = Int64.Type, Unit Price = Currency.Type, Description = text], { {2,3,"Car"}, {4,5,"Bike"} } ), Custom1 = Table.ColumnsOfType(Source,{type number}) in Custom1
And if I have a nested table in a query, it never returns anything if I add a custom column to return column names of a specific data type.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIndeed many thanks to @LarsSchreiber
His solution worked for me too with the numeric type as follows:
Table.ColumnsOfType(Patients1, {type nullable number})
Hi @edhans,
But the following incorrectly returns both the Quantity and Unit Price fields.
let Source = #table( type table [Quantity = Int64.Type, Unit Price = Currency.Type, Description = text], { {2,3,"Car"}, {4,5,"Bike"} } ), Custom1 = Table.ColumnsOfType(Source,{type number}) in Custom1And if I have a nested table in a query, it never returns anything if I add a custom column to return column names of a specific data type.
What do you want this query return? Both of the int type and Currency type should be the number type in Power Query.
If it is convenient, please share your desired output so that we could help further on it.
Best Regards,
Cherry
Well, I would expect it to return the column types that match the types I specify, which is what the documentation indicates: "Returns a list with the names of the columns that match the specified types."
So types would be:
If it was more generic, like "type number" returned anything numeric, that would be ok I suppose if the documentation explained how it worked. Since "type date" is just a formatted integer, would "type number" be expected to return that?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
There is no int64 type in Power BI, so "type number" returned anything numeric.
In addition, by my tests, if we type date with format interger, the "type number" would not return the date column. We should use "type date" to return the date list.
Best Regards,
Cherry
There is Int64.Type in Power BI. There is type number, Currency.Type, and Percentage.Type as well.
And it doesn't work consistently. See this file. type number and type text both return empty lists, but I cannot for the life of me figure out why.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingInteresting. My testing shows that all number types: Number.Type, Int64.Type, Currency.Type, Decimal.Type are treated the same for this function. Number.Type is a kind of generic which includes Int64, Decimal, Currency, etc.
= Table.ColumnsOfType(#"Query", {Int64.Type})
is the same as
= Table.ColumnsOfType(#"Query", {Number.Type})
Well, I didn't even know Number.Type was valid. Seems to be identical to "type number" when typing a column though.
And I still cannot get it to work in my example file. type number or Number.Type returns a empty list.
I think this is a poorly documented function, and a buggy one. But I'd love it if someone could jump in with the correct syntax.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingit seems it only recognises the primitive types as per language specification
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-language-specification
if you look in #shared, and filter for .Type, as below
let Source = #shared, #"Converted to Table" = Record.ToTable(Source), #"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.EndsWith([Name], ".Type")) in #"Filtered Rows"
you can see that all the Int64, Currency etc. all have type number, which I assume is what the function is getting
Interesting. That is helpful as to when it works why it works the way it does. The documentation needs to specify this IMHO. I would try the "Contribute" feature to add this, except I cannot get it to work consistently, as in the empty list in my example above.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @edhans,
Have you solved your problem?
If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please feel free to ask.
Best Regards,
Cherry
I Cherry. No, it isn't resolved. I cannot get it to work reliably as noted above. Sometimes it pulls broad data (like integers, numbers, percents) when i use type number, which is ok I suppose (the documentation isn't clear on this at all), and other times, it pulls nothing.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFor anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such
Table.ColumnsOfType(Patients1, {type nullable text})
Lars Schreiber's investigation, solution and explanation from blog link above worked for me!
@hansei wrote:For anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such
Table.ColumnsOfType(Patients1, {type nullable text})
Thanks @LarsSchreiber
User | Count |
---|---|
109 | |
82 | |
74 | |
53 | |
49 |
User | Count |
---|---|
133 | |
97 | |
94 | |
71 | |
69 |