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,



 

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.

 

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

  • edhans's avatar
    edhans
    Community Champion
    7 years ago

    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?

     

     

    • 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.