none
Use "all" in the filter RRS feed

  • Question

  • Hi,

    Thanks to Imke Feldmann, my last question was answered. 

    https://social.technet.microsoft.com/Forums/en-US/4ef13e27-5c41-485e-8662-5dfa6e20abef/dynamic-join-in-power-query?forum=powerquery#344bc7d2-0a2f-4d6f-802c-a626f97858df


    I have an improved question. Order is ranged from 1 to 5, place is ranged from 11 to 14. 

    Per the below table, when (order=1 and place=11) or (order =2 and place=11), they are grouped into group a. The same for the others. Group b: when (order=3 and place=12) or (order =4 and place=14); Group c: when order =5 and (place =11 or 12 or 13 or 14 or 15).

    Order Place Group
    1 11 a
    2 11 a
    3 12 b
    4 14 b
    5 11 c
    5 12 c
    5 13 c
    5 14 c

    The question is how to use the below table to replace the above table: using "all" means no filter for that subcategory.

    Order Place Group
    1 11 a
    2 11 a
    3 12 b
    4 14 b
    5 All c

    How to fulfill this "all" function in the filter?

    Thanks

    Monday, October 5, 2015 4:07 PM

Answers

  • You simply need to add this step between the Tables and Group-Step:

    = Table.SelectRows(Tables, each ([Value] <> "All"))

    This will eliminate the Place as a criteria to be matched upon.

    LinkToFile

    Query: xlsCombination


    Imke Feldmann TheBIccountant.com

    Monday, October 5, 2015 5:08 PM
    Moderator
  • Hi Ken,,

    this is an error - would have been just too easy :-)

    You seem to have spotted the right way to go here :-) :

    Need to create separate tables that only contain columns with filter values in it. Did it and created a function call for the different table sizes.

    As this was on my list for a client anyway, I just finished it off with some additional logical conditions to be selected in column “Type”:

    • "AndOr" is what you have had so far (The rows have to contain any of the values in each column) Columns with “All” don’t have to match.
    • "AllAnd": all criteria have to be matched within the same row
    • "AllOr": Any matching field of any column is enough.

    Please let me know, if you detect any errors (would save me some time then when I finish it off for the client J).

    LinkToFile


    Imke Feldmann TheBIccountant.com

    Tuesday, October 6, 2015 3:30 PM
    Moderator

All replies

  • You simply need to add this step between the Tables and Group-Step:

    = Table.SelectRows(Tables, each ([Value] <> "All"))

    This will eliminate the Place as a criteria to be matched upon.

    LinkToFile

    Query: xlsCombination


    Imke Feldmann TheBIccountant.com

    Monday, October 5, 2015 5:08 PM
    Moderator
  • Hi Imke,

    Thanks for the reply. I need to play with it more. One question: why acct=1023  will be selected when the filter says below? It should only select data when acct=1005, and dept=c.

    1005 All c

    Thanks,

    Ken

    Monday, October 5, 2015 7:15 PM
  • Hi Ken,,

    this is an error - would have been just too easy :-)

    You seem to have spotted the right way to go here :-) :

    Need to create separate tables that only contain columns with filter values in it. Did it and created a function call for the different table sizes.

    As this was on my list for a client anyway, I just finished it off with some additional logical conditions to be selected in column “Type”:

    • "AndOr" is what you have had so far (The rows have to contain any of the values in each column) Columns with “All” don’t have to match.
    • "AllAnd": all criteria have to be matched within the same row
    • "AllOr": Any matching field of any column is enough.

    Please let me know, if you detect any errors (would save me some time then when I finish it off for the client J).

    LinkToFile


    Imke Feldmann TheBIccountant.com

    Tuesday, October 6, 2015 3:30 PM
    Moderator
  • Hi Imke,

    I am still working on it. What I am thinking is that split the filters into two parts: without all and with all. Inner join the two parts with the data independently and then append two queries. 

    It may take some extra steps but I think it will work.

    Thanks,

    Ken

    Tuesday, October 6, 2015 4:56 PM