Forum Discussion
Has anyone been able to reliably use Table.ColumnsofType() in Power Query?
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
Custom1But 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.
14 Replies
Indeed many thanks to @LarsSchreiber
His solution worked for me too with the numeric type as follows:
Table.ColumnsOfType(Patients1, {type nullable number})I cannot believe I've been looking for this answer everywhere and this finally solved my problem. I was trying to bring back a list of all my text columns so I could replace text in all text columns but was only getting back 2 columns that had no nulls in them. 😎 This was the answer and SO simple.
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:
- type text
- type number
- Int64.Type
- type logical
- Percentage.Type
- etc.
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?
Hi 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
