Remove Null Rows

If the entire row is null, just click the filter icon at the top of any column and un-check (null)

HTH,
 
Thanks Ken, i didnt realize its that easy. By the way I just purchase your book yesterday, M is for Data Monkey. I think its an awesome book.
 
In my view the applicable option here would be on the Home tab - Remove Rows - Remove Blank Rows.

Remove blank rows.png

This will remove rows that have null or blank in all columns (not just 1 column) by generating code like:
Code: 
= Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
 
I see, i think that is actually a better solution since it validates all the columns whilst filter only works on one... Great!!!
 
Interesting, Marcel. I've never actually used that option, as I've been filtering nulls since before that button existed.

They'll do different things, of course. My recommendation will only remove rows where a null is present, but would ignore blanks. Yours will only get rows that are entirely blank.

Most times I'd say they will do the same. Nice to know we have options here. :)
 
if with null removal

I am parsing ICS files for a calendar booking system. It contains some relevant events and some that are only wanted to provide an audit trail. Events only wanted for the audit trail can be filtered out as they have null entries on the booking fields. Each event has an arbitrary user created value and so can not be filtered from a prepared list. The plan is to provide the user with a choice of true (filter out null) or false (show all events in the query). However, two problems emerge:
(1) The dynamic filter is a worksheet range name with the contents sourced from a dropdown list. However, it appears that this value is cached and refreshing the query does not update it. I need to exit Excel and reload to have the PQ update this value. Is there a way to force the value in the PQ to update?
(2) I am struggling with the syntax to write the if statement for the filter. The following fails:
if OnlyClinicBookings = "TRUE" then Table.SelectRows(#"Sorted Rows", each ([Purpose] <> null) else each true)

Are you able to help with these issues? Thanks.



MarcelBeug said:
In my view the applicable option here would be on the Home tab - Remove Rows - Remove Blank Rows.

View attachment 6590

This will remove rows that have null or blank in all columns (not just 1 column) by generating code like:
Code: 
= Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
Click to expand...
 
You must log in or register to reply here.
Back
Top