Forum Discussion
Helper IHow to use list.contains from another query
Hi cheryl-lee33
The problem is once you use the List.Contains, you are limited in scope as to what it can see. You need to assign your values you want compared to a variable first. So something like this, as viewed in the Advanced Editor:
#"Add Column" = Table.AddColumn( #"Changed Type", "New Column", each let varFieldValue = [Local authorities] in List.Contains( Table.Column(#"Source 2", "local authority - Group"), varFieldValue ) )This will return a true/false column, which you can then filter.
Community ChampionHi cheryl-lee33
The problem is once you use the List.Contains, you are limited in scope as to what it can see. You need to assign your values you want compared to a variable first. So something like this, as viewed in the Advanced Editor:
#"Add Column" =
Table.AddColumn(
#"Changed Type",
"New Column",
each let
varFieldValue = [Local authorities]
in
List.Contains(
Table.Column(#"Source 2", "local authority - Group"),
varFieldValue
)
)
This will return a true/false column, which you can then filter.
- lbendlin
Super User
edhans What is faster, a List.Contains or a left outer join where the right table is the lookup table?
- edhans
I think the joins are generally faster than List.Contains when folding as List.Contains generates an IN statement in SQL, which does't seem as fast to me as a JOIN is, but I've not done any performance tests. But the nice thing about List.Contains() is your list can be outside of SQL and folding still works. So if you are merging a SQL table with an Excel table, that breaks folding, and I think List.Contains will work faster because an IN statment in SQL is still faster than bringing ALL of your data to your desktop and letting the mashup engine do all of the work.
YYMV. No absolutes. Test and see which works best!
hiedhans this is exactly my observation. But it refers to the cheryl-lee33 code, not yours.
cheryl-lee33 attempted with the statementTable.SelectRows(Source, each List.Contains(#"Source 2"[#"local authority - Group"], [Local authorities]))
which probably gave him the result of an invalid identifier.
Like this:
If instead he had used the code like this and everything works fine.
In the absence of further information, I hypothesized the following situation:
ciao
