Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

List.Contains and List.Startswith

I have a question regarding a problem in power query M

Lets say I have table A with multiple projet 

a-bc

a-et,

h-gf,

c-df,

a-2d,

And I have a table B with project prefix 

a,

d,

c, 

I would like to filter the Table A with Table B. Since I have multiple table, I would only be required to change table B (add more prefix or remove)  instead of going in each table and put a filter with "startswith".

After filtering, the Table A would look like this

a-bc,

a-et,

c-df,

a-2d,

Thank you !

Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    4 years ago

    Hi Anonymous 

     

    Download sample PBIX file with solution code

     

    Set up both tables and load into Power Query.

    You can then filter the Projects table with the Prefix table with this line

     

    = Table.SelectRows(Source, each List.ContainsAny(Text.ToList(Text.Start([Project],1)), Prefix[Prefix]))

     

    Regards

    Phil

1 Reply

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    4 years ago

    Hi Anonymous 

     

    Download sample PBIX file with solution code

     

    Set up both tables and load into Power Query.

    You can then filter the Projects table with the Prefix table with this line

     

    = Table.SelectRows(Source, each List.ContainsAny(Text.ToList(Text.Start([Project],1)), Prefix[Prefix]))

     

    Regards

    Phil