none
Remove rows which consists of specific value in a specific column RRS feed

  • Question

  • Hi,

    How do I remove the row which consist of a specific value ("Total") in a specific column ("Customer") in power query, example as below:

    Product Customer Amount
    Fish Aesen                   555
    Fish Blanko                   555
    Fish Total               1,110
    Meat Isha                   111
    Meat Siri                   111
    Meat Total                   222

    Thank you very much in advance

    Friday, June 26, 2015 9:08 AM

Answers

  • A Filter on the column - textfilter - does not equal: Choose "Total"

    = Table.SelectRows(#"Changed Type", each [Customer] <> "Total")


    Imke

    • Marked as answer by BlackRabbit 7 Friday, June 26, 2015 10:17 AM
    Friday, June 26, 2015 10:02 AM
    Moderator
  • Exactly, that's why there are multiple types of filters to cater for this different needs.

    So if you would set an additional "equals fish & meat" filter on product, "vege" wouldn't show up in the future.

    = Table.SelectRows(#"Changed Type", each [Product] = "fish" and [Product] = "meat")

    #"Changed Type" is the name of the previous step (if you don't see this in your preview window, check "Show formula bar" in "View")


    Imke

    • Marked as answer by BlackRabbit 7 Sunday, June 28, 2015 10:34 AM
    Saturday, June 27, 2015 6:20 AM
    Moderator
  • Not sure if I got your question right, but does this answer it?

    The filter-types that are shown differ depending on the type of the column (different for numbers and dates)

    I can highly recommed Chris Webbs Power Query book. It describes all basics very well and has a very good table of contents so that it also does a good job as a reference book.


    Imke

    • Marked as answer by BlackRabbit 7 Sunday, June 28, 2015 3:18 PM
    Sunday, June 28, 2015 11:14 AM
    Moderator

All replies

  • A Filter on the column - textfilter - does not equal: Choose "Total"

    = Table.SelectRows(#"Changed Type", each [Customer] <> "Total")


    Imke

    • Marked as answer by BlackRabbit 7 Friday, June 26, 2015 10:17 AM
    Friday, June 26, 2015 10:02 AM
    Moderator
  • Hi Thank you very much. I now understand better regarding the filtering function.

    So the <> sign will clear off those that I un-tick in the filter.

    Then what if I just "fish" and "meat" to be selected all the time without including any new product "vege" if any in the future?

    How will the formula works? Sorry another further question.

    By "Changed Type" means what?

    Friday, June 26, 2015 10:21 AM
  • Exactly, that's why there are multiple types of filters to cater for this different needs.

    So if you would set an additional "equals fish & meat" filter on product, "vege" wouldn't show up in the future.

    = Table.SelectRows(#"Changed Type", each [Product] = "fish" and [Product] = "meat")

    #"Changed Type" is the name of the previous step (if you don't see this in your preview window, check "Show formula bar" in "View")


    Imke

    • Marked as answer by BlackRabbit 7 Sunday, June 28, 2015 10:34 AM
    Saturday, June 27, 2015 6:20 AM
    Moderator
  • My apology for the late reply. Thank you very much for the clarification.

    May I know where I can find the type of Filter formula/language? 

    Sunday, June 28, 2015 10:34 AM
  • Not sure if I got your question right, but does this answer it?

    The filter-types that are shown differ depending on the type of the column (different for numbers and dates)

    I can highly recommed Chris Webbs Power Query book. It describes all basics very well and has a very good table of contents so that it also does a good job as a reference book.


    Imke

    • Marked as answer by BlackRabbit 7 Sunday, June 28, 2015 3:18 PM
    Sunday, June 28, 2015 11:14 AM
    Moderator
  • Thank you very much. This helps a lot and You have answered my question. I will read the book too. Thank you very much for your help and recommendation.

    Sunday, June 28, 2015 3:21 PM
  • Hi,

    how can i refer to a specific cell instead of its value?

    For example, i would like to get data from column Customer row 6

    Thank you!

    Wednesday, December 21, 2016 8:01 AM
  • You have to explicitly add a column that contains the row number, by adding an Index-column. If you let this start with 1 instead of the default-value 0, you then apply your select-statement on that column.

    For advanced concepts on how to work "Excel-cell"-style check out this article:

    http://excel-inside.pro/blog/2015/11/05/absolute-and-relative-references-in-power-query/


    Imke Feldmann TheBIccountant.com

    Wednesday, December 21, 2016 8:24 AM
    Moderator