locked
Filtering using AND RRS feed

  • Question

  • I have a table  (basic example dataset below)

    ID 		EntityID		SourceFile 		Cost
    1 		00A01  		file1.txt 			20
    2 		00A03  		file1.txt 			20
    3 		00E07  		file2.txt 			20
    4 		00A06  		file2.txt 			20
    5 		00A04  		file1.txt 			20
    6 		00E05  		file2.txt 			20
    7 		00A06  		file2.txt 			20
    8		00A04  		file1.txt 			20
    9		00E05  		file2.txt 			20


    I imported this into powerquery.
    I now need to remove rows that have a sourceFile = file2 AND EntityID contains "%E%"
    Im not sure how to add this condition to the powerquery M definition code. 

    Closest i have come is with the below, but this is removing all rows with a SourceFile = "File2.txt", not just ones that also has an EntityID that contains "E".

    Table.SelectRows(myTable, each ([SourceFile] <> "File2.txt"  and Text.Contains([EntityID],"E")))

    All help appreciated.




    Friday, August 15, 2014 9:57 AM

Answers

  • I ended up doing this but it felt very much like a hack. I was hoping for a better solution but if this is the best then il stick with it, thanks!

    Rethinking the filter requirement, it occurred to me that you can restate your original formula as:

    Table.SelectRows(Source, each ([SourceFile] <> "file2.txt" or not Text.Contains([EntityID],"E")))

    Monday, August 18, 2014 4:30 PM

All replies

  • Hi Winston,

    Filters on a table are additive, so the first filter is applied, then the second is added, and so on. One way to achieve your goal would be to create a custom column with a formula like:

    Text.At([EntityID],2)&[SourceFile]

    The result will be values Afile1.txt, Afile1.txt, Efile2.txt....Efile2.txt. You can then filter out the rows containing the Efile2.txt values. 

    Saturday, August 16, 2014 2:45 PM
  • I ended up doing this but it felt very much like a hack. I was hoping for a better solution but if this is the best then il stick with it, thanks!
    Monday, August 18, 2014 3:45 PM
  • I ended up doing this but it felt very much like a hack. I was hoping for a better solution but if this is the best then il stick with it, thanks!

    Rethinking the filter requirement, it occurred to me that you can restate your original formula as:

    Table.SelectRows(Source, each ([SourceFile] <> "file2.txt" or not Text.Contains([EntityID],"E")))

    Monday, August 18, 2014 4:30 PM