cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianb91
Frequent Visitor

Conditional Filter based on multiple parameters in Power Query

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
brianb91
Frequent Visitor

Re: Conditional Filter based on multiple parameters in Power Query

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.

View solution in original post

Community Support
Community Support

Re: Conditional Filter based on multiple parameters in Power Query

Hi @brianb91 ,

 

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.

 

move.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

2 REPLIES 2
brianb91
Frequent Visitor

Re: Conditional Filter based on multiple parameters in Power Query

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.

View solution in original post

Community Support
Community Support

Re: Conditional Filter based on multiple parameters in Power Query

Hi @brianb91 ,

 

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.

 

move.PNG

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors