cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
12 REPLIES 12
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.

 

query.png

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

There is Int64.Type in Power BI. There is type number, Currency.Type, and Percentage.Type as well.

 

image.png

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.

 

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

 

 

 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Stachu
Community Champion
Community Champion

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

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

 

 

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 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.