none
Filtering and Deleting Rows RRS feed

  • Question

  • I am not very versed in writing syntax, especially in Power Query's "Query Editor". I want to create a filter based on two variables - [Status] (containing "D" or "C") and [LastActivity] (containing dates, but some are 'null').

    I want to remove rows that are just "D" for [Status] and "null" for [LastActivity]. (Note: I want to keep the "D"s that have dates in [LastActivity] as well as "C"s that don't have dates in [LastActivity])

    I thought I could use "Table.RemoveRows"?? Not sure.

    I would appreciate any help. Thank you.





    • Edited by CMcReynolds Wednesday, April 8, 2015 3:12 PM
    Wednesday, April 8, 2015 2:59 PM

Answers

  • No, the syntax for Table.RemoveRows only refers to row numbers (sort of), you cannot define filters there.

    Strange as it looks, I think your filter-Expression will be this:

    Table.SelectRows(PreviousStep, each [Status] <> D or [LastActivity] <> null )


    Imke

    • Marked as answer by CMcReynolds Wednesday, April 8, 2015 8:14 PM
    Wednesday, April 8, 2015 4:08 PM
    Moderator
  • Sorry, should have mentioned that you might need to put a "#" before your stepname:


    Imke

    • Marked as answer by CMcReynolds Thursday, August 6, 2015 12:18 PM
    Thursday, August 6, 2015 5:02 AM
    Moderator

All replies

  • No, the syntax for Table.RemoveRows only refers to row numbers (sort of), you cannot define filters there.

    Strange as it looks, I think your filter-Expression will be this:

    Table.SelectRows(PreviousStep, each [Status] <> D or [LastActivity] <> null )


    Imke

    • Marked as answer by CMcReynolds Wednesday, April 8, 2015 8:14 PM
    Wednesday, April 8, 2015 4:08 PM
    Moderator
  • Wouldn't that get rid of all "D"s?
    Wednesday, April 8, 2015 7:00 PM
  • No, amazingly not :-)

    Imke

    Wednesday, April 8, 2015 7:26 PM
    Moderator
  • I have stared at that for most of the day and I can't figure out WHY it works, but it does. lol I'll leave it as something "magical" that I'm not meant to understand, like Peeps.
    Wednesday, April 8, 2015 8:14 PM
  • Table.SelectRows effectively expects a description of the rows that you want to keep in the result set. In this case, it says "I want to keep all of the rows where the status is not D or the last activity is not null." According to De Morgan's law, this is the equivalent of saying "I want to discard all the rows where the status is D and the last activity is null". It's not magic; it's simple Boolean logic! :)
    Wednesday, April 8, 2015 9:09 PM
  • Hi,

    I have a related Query. I would like to keep 'CustID' and the newest 'Date created' . How do I do that? 


    Lars Wärvik

    Wednesday, May 6, 2015 6:37 PM
  • Hi Lars,

    if you sort your table on 'Date created' in descending order and then remove duplicates on 'CustID', you should be left with the newest entry per CustID.

    Is that what you're looking for?


    Imke

    Thursday, May 7, 2015 3:26 AM
    Moderator
  • Thanks Imke,

    Actually I used Group BY CustID and Max(Date created) and it worked fine.


    Lars Wärvik

    • Proposed as answer by larraw Thursday, May 7, 2015 1:58 PM
    Thursday, May 7, 2015 1:58 PM
  • Imke - how do I obtain the table/source name? It says PreviousStep is not recognized.

    Also, just for those who are reading, D has to be in quotes ("D") because my field is a text field

    Wednesday, August 5, 2015 8:47 PM
  • In the Power Query window right hand you see the section "Query Settings" in there "Applied Steps". In the formula I've provided you need to replace "PreviousStep" by the stepname of your step that comes before this filter step.

    Thanks for the correction of the missing "".


    Imke

    Wednesday, August 5, 2015 9:03 PM
    Moderator
  • Sorry, should have mentioned that you might need to put a "#" before your stepname:


    Imke

    • Marked as answer by CMcReynolds Thursday, August 6, 2015 12:18 PM
    Thursday, August 6, 2015 5:02 AM
    Moderator
  • Perfect! Thank you Imke :)
    Thursday, August 6, 2015 12:18 PM