Forum Discussion

edhans's avatar
edhans
Community 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
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

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

edhans's avatar
edhans
Community Champion
7 years ago

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.

 

 

  • freder1ck's avatar
    freder1ck
    Kudo Kingpin
    7 years ago

    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})

     

     

     

     

    • edhans's avatar
      edhans
      Community Champion
      7 years ago

      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.

      • Stachu's avatar
        Stachu
        Community 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