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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Dicken
Responsive Resident
Responsive Resident

Table Contains syntax explanation

  
An example of Table.Contains has the following  

 

Table.Contains( 
    Table.FromRecords( {
        [CustomerID = 1, Name = "Bob", Phone = "123-4567"],
        [CustomerID = 2, Name = "Jim", Phone = "987-6543"],
        [CustomerID = 3, Name = "Paul", Phone = "543-7890"],
        [CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
    } ),
    [CustomerID = 4, Name = "Bob"],
    "Name"
 )

But you could just re write as ; are there reasons why or is it just a cose the you have columns stored as reocrds and just want to specify a partiuclar one ? 

 

= Table.Contains(
Table.FromRecords( {
[CustomerID = 1, Name = "Bob", Phone = "123-4567"],
[CustomerID = 2, Name = "Jim", Phone = "987-6543"],
[CustomerID = 3, Name = "Paul", Phone = "543-7890"],
[CustomerID = 4, Name = "Ringo", Phone = "232-1550"]
} ),
[Name = "Bob"]
)  

 I cant find any information as t why include to columns and then specify  a true false from only one , why not just write ; 

 


Are there reasons I'm overlooking as to why 

Richard

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

It's so that you can match on the name value in a specific record, whatever that name value might be--it might not always be "Bob". So if you have a list of records, which is what a table is, and you want to check if another table contains the name that is in the current row/record. This is especially useful when you have a column of nested tables, so if you have a "Name" column in your table, and you have a column of nested tables that also have a "Name" column, and you want to keep rows where "Name" matches the "Name" in the nested table. So 

 

each Table.Contains([NestedTableColumnName, _, "Name")

 

--Nate

View solution in original post

PwerQueryKees
Super User
Super User

Good question. As the official documentation tells us the third parameter should be a comparere function, not a string....

I do see why you would want to name a column (is a list of columns also supported??) to limit the comparison to if the record for the first parameter comes from another table, because it saves a Table.SelectColumns(). At least a convenience and possible a perfromance advantage.

View solution in original post

6 REPLIES 6
PwerQueryKees
Super User
Super User

Good question. As the official documentation tells us the third parameter should be a comparere function, not a string....

I do see why you would want to name a column (is a list of columns also supported??) to limit the comparison to if the record for the first parameter comes from another table, because it saves a Table.SelectColumns(). At least a convenience and possible a perfromance advantage.

Thanks, 

 

RD

Anonymous
Not applicable

It's so that you can match on the name value in a specific record, whatever that name value might be--it might not always be "Bob". So if you have a list of records, which is what a table is, and you want to check if another table contains the name that is in the current row/record. This is especially useful when you have a column of nested tables, so if you have a "Name" column in your table, and you have a column of nested tables that also have a "Name" column, and you want to keep rows where "Name" matches the "Name" in the nested table. So 

 

each Table.Contains([NestedTableColumnName, _, "Name")

 

--Nate

Dicken
Responsive Resident
Responsive Resident

That's prettey much what I thought as I couldn't think of another use, but thought there might be something  overlooked.

tharunkumarRTK
Super User
Super User

@Dicken 

I guess this article answers your question: 

Screenshot 2024-09-07 at 6.49.30 PM.png

https://powerquery.how/table-contains/

 

Need a Power BI Consultation? Hire me on Upwork

 

 

 

Connect on LinkedIn

 

 

 








Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

Proud to be a Super User!


PBI_SuperUser_Rank@2x.png

Not really as that is what provoked the qustion; differece; 

 [Name = "Bob"]

and ; 

 [CustomerID = 4, Name = "Bob"],
    "Name"

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.