Forum Discussion
How to remove rows ONLY based on specific column fields
Hello,
I have a table where I want to be able to remove rows that have null/blank values but only for specific columns. Using the table below as an example, my table will always have an ID and Location. However, I only want to remove the rows where a null/blank value appears in all columns of Quantity, Due Date, Description, and Order Date. So, ID numbers 6 and 10 will get removed below even though there is still data in the first 2 columns. I am trying to use conditional columns to achieve this, but not sure if this is the best way. Any help would be greatly appreciated, thanks!
ID | Location | Quantity | Due Date | Description | Order Date |
1 | A | 8 | 2/3/25 6:00 | AA | null |
2 | B | null | 2/20/25 14:00 | BB | null |
3 | C | 9 | 10/3/24 13:00 | null | null |
4 | D | 7 | 2/6/24 6:00 | CC | 8/8/25 15:30 |
5 | E | 7 | 1/3/26 6:30 | DD | 8/8/25 15:30 |
6 | F | null | null | null | null |
7 | G | null | 8/19/25 5:45 | EE | null |
8 | H | 7,8 | 7/3/25 10:00 | null | null |
9 | I | 2 | 6/17/24 17:50 | null | null |
10 | J | bull | null | null | null |
Hi dolphin18
Here's how I would do it:
Add a step with this code in the formula bar, where PreviousStep is the previous step in the query:
= Table.SelectRows( PreviousStep, each List.NonNullCount({[Quantity], [Due Date], [Description], [Order Date]}) > 0 )This selects rows where at least one of those four columns is non-null.
Does this work for you?
2 Replies
Hi dolphin18
Here's how I would do it:
Add a step with this code in the formula bar, where PreviousStep is the previous step in the query:
= Table.SelectRows( PreviousStep, each List.NonNullCount({[Quantity], [Due Date], [Description], [Order Date]}) > 0 )This selects rows where at least one of those four columns is non-null.
Does this work for you?
This worked! Thank you so much!!
