Results 1 to 5 of 5

Thread: Filtering in Power Query

  1. #1
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus

    Red face Filtering in Power Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 )
    Regards,
    Rudi
    www.eileenslounge.com

  2. #2
    Q1: No UI.
    Try Text.Range
    Q2: Try Merge Queries.

    http://www.mediafire.com/download/9a...12_10_15a.xlsx


  3. #3
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    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?
    Regards,
    Rudi
    www.eileenslounge.com

  4. #4
    With oodles of possibilities, there is never a "right" answer.

  5. #5
    Conjurer Rudi's Avatar
    Join Date
    Jan 2014
    Location
    Cape Town, RSA
    Posts
    134
    Articles
    0
    Excel Version
    Excel 365 ProPlus
    Cheers; and TX again!
    Regards,
    Rudi
    www.eileenslounge.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •