CODEWITHSUNDEEP
excelpowerquery
qwer11121
qwer11121

Reputation: 168

How to make a fuzzy filter in Power Query according to a list

I want to filter a Power Query table according to another list:

The fact table is:

Location    Name

MEL/1F/101  zmel
SHA         zsha
BKK/2F      zbkk
SGN         zsgn

And the lookup list is

{"BKK","SHA"}

The result I want is 

Location    Name

SHA         zsha
BKK/2F      zbkk

Now I use 

l={"SHA","BKK"},
b=Table.SelectRows(#"Expanded Column1", each List.Contains(l,[location]))

but the BKK/2F is omitted, only SHA shows.

Does any one knows to correct this? Thanks.

Upvotes: 0

Views: 164

Answers (1)

aaa
aaa

Reputation: 858

You can create a new column using conditional Column by referencing to the table column that contains SHA & BKK. Replace the Column Name to your column.

You can use fill down function if you want to get rid of the nulls.

Update

For your case you might want to use the operator begins with since your BKK has extra text behind

enter image description here

Upvotes: 1

Related Questions