Conditional Filter based on multiple parameters in Power Query

[Anonymous]

Full disclousure, I'm a begineer in Power Query and have trying to solve a problem I've been having. I've searched the forums and I couldn't find anything that applied to my situation, but I might be wrong.

So I have a query with 4 parameters. They are the Sales Order Number, the Job Number, The Project Number, and The Purchase Number. I want to make an excel power query that will allow the user to filter the power query by any one of these parameters. Right now I have the macro working to always filter by one variable, like I show below.

#"Filtered Rows2" = Table.SelectRows(#"Sorted Rows1", each ([JOB] = JOB_NUM)),

This code from the advanced editor allows me to filter the column JOB by the Parameter I've set up JOB_NUM. However, I only want to filter by the one parameter that is not null. (My macro will ensure that the other parameters are null.)

I thought maybe an if statement would work. This is what I'm working with at the moment.

if JOB_NUM <> null then #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows1", each ([JOB] = JOB_NUM)) else if SALES_NUM <> null then #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows2", each ([SALES] = SALES_NUM)) else if PROJ_NUM<> null then #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows3", each ([PROJECT] = PROJ_NUM)) else if PURCH_NUM <> null then #"Filtered Rows2" = Table.SelectRows(#"Sorted Rows4", each ([PURCH] = PURCH_NUM))

This isn't working. I'm very new to this and I was hoping to get some help? I'm using office 2010, with 32 bit power query installed.

[Anonymous · Accepted solution]

I've figured out that I can conditionally filter my data by using the following statements

 
    #"Filtered Rows2" = if JOB_NUM <> "" then Table.SelectRows(#"Reordered Columns8", each [JOB] = JOB_NUM) else each [JOB],
    #"Filtered Rows3" = if SALES_NUM <> "" then Table.SelectRows(#"Filtered Rows2", each [SALES_ORDER] = SALES_NUM) else each [SALES_ORDER]
in
    #"Filtered Rows3"

However now my problem is that whichever if statement is called first, that is the one that it filters by. In this situation if JOB_NUM is null, then the table displays nothing. However if I rearrange it so the SALES_NUM if statement precides it, then the opposite thing happens.

I'm getting closer.

[Anonymous · Accepted solution]

I've figured out that I can conditionally filter my data by using the following statements

 
    #"Filtered Rows2" = if JOB_NUM <> "" then Table.SelectRows(#"Reordered Columns8", each [JOB] = JOB_NUM) else each [JOB],
    #"Filtered Rows3" = if SALES_NUM <> "" then Table.SelectRows(#"Filtered Rows2", each [SALES_ORDER] = SALES_NUM) else each [SALES_ORDER]
in
    #"Filtered Rows3"

However now my problem is that whichever if statement is called first, that is the one that it filters by. In this situation if JOB_NUM is null, then the table displays nothing. However if I rearrange it so the SALES_NUM if statement precides it, then the opposite thing happens.

I'm getting closer.

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

Here #"Filtered Rows2" is called first.

As you can check the steps in the APPLIED STEPS pane of power query by moving down/up to know which will be called first.