Forum Discussion

cheryl-lee33's avatar
cheryl-lee33
Icon for Helper I rankHelper I
6 years ago
Solved

How to use list.contains from another query

Hi,

I have two queries: one query (source 1) has data field called "Local Authorities" and record count, and the other query (source 2) has the data of "local authority group" and "local authority".  I want to limit the records in the first query by showing "Local Authorities" matching the field "local authority group" in the second query.  

 

How can I construct the codes using list.contains?

I tried this, but it did not work.

= Table.SelectRows(Source, each ListList.Contains(#"source 2"[local authority - Group], [Local authorities])

 

Any help is much appreciated!

 

Thanks,

Cheryl

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

     

13 Replies

  • edhans's avatar
    edhans
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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.

     

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      6 years ago

      edhans What is faster, a List.Contains or a left outer join where the right table is the lookup table?

      • edhans's avatar
        edhans
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    as far as I can guess at the data structure involved, the only problem with your solution seems to me to be the identifier

     [local authority - Group] that has the "-".

     

    try putting it between #" "  so that is [#"local authority - Group"]
    Everything should work.

     

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      6 years ago

      Good catch Anonymous , but not necessary. If I were referring to it as [field - name] I'd need that construct, but inside quotes, I do not.

       

      so:

      • [#"Field - Name"]
      • [Field Name]
      • "Field - Name" (as in the name of the column, not a field refrence)

      all work. 

  • camargos88's avatar
    camargos88
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi cheryl-lee33 ,

     

    You can do like on the Source1 query like:

     

    1) Create a new custom column and use this code:

     

    let _localAuthorities = [Local Authorities] in 

    List.Contains(Source2[local authority group], _localAuthorities)

     

    And filter the result to exclude the nonmatching values.

     

    Just be aware you might to apply trim/clean functions in order to compare text values.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

     

    hi cheryl-lee33 

    could you, please, show a small example of data and a table with the expected result?
    For me, for example, the structure of the second table is unclear.

    • cheryl-lee33's avatar
      cheryl-lee33
      Icon for Helper I rankHelper I
      6 years ago

      Thanks for thinking about this.  It has been resolved.  If you still want me to post the sample, I'm happy to sort this out in the morning.

       

      Best regards!