cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zenisekd
Helper I
Helper I

Advanced filtering in power query - multiple conditions

Hi all, 
I bumped into a puzzle that I do not understand...

 

In power query I have two columns - State (that gives values Cancel, Sales, etc..) and Confirmation Date (gives null or specific date).

 

I want to filter all rows that contain at the same time Cancel and null. I tried to use Filter Rows - advanced filter and .... it doesnt work. It filters all Cancel and all nulls ... I cant see why... I am not sure about the code, but the logic in the user interface function should clearly work this way, but it doesnt .... or am I missing something?

 

I managed to find another way to filter the rows, by adding new column and then filtering by it... but I would like to do it in one easy step... not three. Can somebody tell me what is going on?

 

= Table.SelectRows(#"Added Custom", each [public.sale_order.confirmation_date] <> null and [state] <> "cancel")

 

zenisekd_0-1612346232443.png

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Please try this instead

 

= Table.SelectRows(#"Added Custom", each not ([public.sale_order.confirmation_date] = null and [state] = "cancel"))

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User IV
Super User IV

Please try this instead

 

= Table.SelectRows(#"Added Custom", each not ([public.sale_order.confirmation_date] = null and [state] = "cancel"))

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

You are the boss, sir 🙂 

However, do you think, that the user interface window just doesn't function properly (giving incorrect code), or am I doing something wrong?

 

Thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors