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
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident 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
Icon for Community Champion rankCommunity 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?