CODEWITHSUNDEEP
excelpowerquery
adam gouldie
adam gouldie

Reputation: 261

Power Query Filtering

I'm struggling to do what I thought would be basic filtering. I have a set of data that has three columns.

Product - Location - Months
Car - UK - 201701
Car - UK - 201702
Car - FR - 201701
Car - FR - 201704
Bike - UK - 201701
Bike - FR - 201703

Now I am trying to filter out lines that I do not want to see which I thought could be achieved by a two column match criteria that contain exact match of UK - 201701.

Unfortunately the advance filter using "AND" criterion removes anything that matches UK and also anything that matches 201701. Which removes all entries of UK and also all entries of 201701.

That is not the desired effect, it should be removing lines that match both UK and 201701 at the same time. But power query seems to be treating the two criteria's as two separate filtering.

Any idea guys how to do this in Power Query?

Upvotes: 0

Views: 141

Answers (2)

Umut K
Umut K

Reputation: 1388

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each [#" Months"] = 201701 and [#" Location "] = " UK ")
in
    #"Filtered Rows"

enter image description here

Upvotes: 1

Peter Hegarty
Peter Hegarty

Reputation: 13

Create Duplicates of Location and Month. Select both new columns, then click Merge. Now filter on this new merged column.

I think this is pretty standard behaviour for table filtering.

Upvotes: 0

Related Questions