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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Dicken
Helper IV
Helper IV

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
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

View solution in original post

PwerQueryKees
Continued Contributor
Continued Contributor

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
Continued Contributor
Continued Contributor

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

watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors