PDA

View Full Version : Filtering in Power Query



Rudi
2015-12-10, 01:35 PM
Hi,

I have two questions on filtering in Power Query

Question 1:

Is there a way to use the Power Query UI to filter for multiple codes?
Say I want to extract only codes starting with 100- or 150- or 200- from hundreds of codes in a column that look like this (bolded ones to be filtered):
103-98-1234
100-56-4356
106-85-9087
150-98-3245
100-54-9834
108-79-5673

I did it in the 'M' code like this:
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each Text.StartsWith([Ph. Number], "100") or Text.StartsWith([Ph. Number], "150") or Text.StartsWith([Ph. Number], "200") or Text.StartsWith([Ph. Number], "250"))

but just want to know if it is possible using ONLY the UI interface.

Question2:

Is it possible to filter a column of codes in a table that is uploaded into Power Query based on codes in another table?
For example; I have a table of thousands of records with codes similar to the ones in Question #1 above, but I have a second table that contains only the codes I want to use as filters (similar to Excel's Advanced Filter feature). I want to filter in Power Query using a reference to another table that acts as the criteria.

Any ideas/solutions would be great.
TX.

PS: Since Ken is the leading Power Query Guru, I find it strange that his forum does not have a dedicated Power Query section. I had to post these questions in "Other Business Applications". Strange??? (Just commenting http://www.excelguru.ca/forums/images/icons/icon11.png )

Herbds7
2015-12-10, 06:42 PM
Q1: No UI.
Try Text.Range
Q2: Try Merge Queries.

http://www.mediafire.com/download/9adrj4bnab6emau/12_10_15a.xlsx (http://www.mediafire.com/download/9adrj4bnab6emau/12_10_15a.xlsx)

Rudi
2015-12-11, 05:39 AM
TX for the reply, Herbds7.
I appreciate your input and time; esp with the sample workbook and screenshot.

Cheers

PS: Does this forum require a "mark as answered" or some other post answer action?

Herbds7
2015-12-11, 07:24 PM
With oodles of possibilities, there is never a "right" answer.

Rudi
2015-12-12, 06:49 AM
Cheers; and TX again!