Forum Discussion

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
7 years ago

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 er...
Need Help
Stachu's avatar
Stachu
Icon for Community Champion rankCommunity Champion
7 years ago

it 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

 

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
7 years ago

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.