Forum Discussion
Has anyone been able to reliably use Table.ColumnsofType() in Power Query?
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
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.
Interesting. 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.
