HELP! How can I apply rule based filtering and row deletion in Power Query

Hi Lauren_Michelle 

Issue #3 - Competitor products accidently included in the data
I would like to delete these...Is there any way to delete rows that include a certain value? (e.g. Delete rows that contain 'compname' in the brand column)

Not quite sure this is the best approach. Let's assume that in Week1 you have unexpected [Brand]s A, B and C and you setup your cleaining query to exclude A,B,C. In Week2 your next CSV brings unexpected [Brand]s A, C, E, F => The last 2 won't be excluded by your query

In an ideal world you should know what your [Brand]s are. With that assumption a possible approach would be:
- Setup a table with your [Brand]s
- Merge that table with the Table from CSV

In the attached example I used Table.FuzzyNestedJoin (instead of Table.NestedJoin) as the function has options that help you better control the matching (Power Query is case-sensitive: a <> A):

// CompetitorsRemoved
let
    Source = TableFromCSV,
    FuzzyMatch = Table.FuzzyNestedJoin(
        Source, {"Brand"},
        TableBrands, {"Brand"},
        "BRANDS", JoinKind.Inner,
        [IgnoreCase=true, IgnoreSpace=false, Threshold=1]
    ),
    RemovedColumn = Table.RemoveColumns(FuzzyMatch,{"BRANDS"})
in
    RemovedColumn

Hope this makes sense, helps a bit and don't confuse you too much