Forum Discussion
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
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
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.
6 Replies
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
I guess this article answers your question:
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!
Not really as that is what provoked the qustion; differece;
[Name = "Bob"]
and ;
[CustomerID = 4, Name = "Bob"], "Name"
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
That's prettey much what I thought as I couldn't think of another use, but thought there might be something overlooked.
