Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
13 REPLIES 13
lucamanunta
Regular Visitor

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

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.