Has anyone been able to reliably use Table.ColumnsofType() in Power Query?

[edhans]

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 error, just nothing.

= Table.ColumnsOfType(Sales,{type text})

The following M code correctly returns the description column:

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 text})
in
    Custom1

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.

[lucamanunta]

Indeed many thanks to @LarsSchreiber

His solution worked for me too with the numeric type as follows:

Table.ColumnsOfType(Patients1, {type nullable number})

[v-piga-msft]

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]

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]

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]

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]

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]

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]

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]

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.

[v-piga-msft]

Hi @edhans,

Have you solved your problem?

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[edhans]

I Cherry. No, it isn't resolved. I cannot get it to work reliably as noted above. Sometimes it pulls broad data (like integers, numbers, percents) when i use type number, which is ok I suppose (the documentation isn't clear on this at all), and other times, it pulls nothing.

[hansei]

For anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such

Table.ColumnsOfType(Patients1, {type nullable text})

[morinsights]

Lars Schreiber's investigation, solution and explanation from blog link above worked for me!

---

@hansei wrote:

For anyone else who may have hit this page, see this blog for insight. The solutions is to expand type list as such

Table.ColumnsOfType(Patients1, {type nullable text})

Thanks @LarsSchreiber