Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pade
Advocate III
Advocate III

Removing empty rows based on a subset of columns

I need to remove empty rows. This is quite easy, just choose "Remove rows", and then "Remove blank rows" from the meny. This will create the formula:

= Table.SelectRows(#"Added Index", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

 

But, I have added an Index column, and that column will never be empty. I also have some other columns that is of no importance when evaluating if the row is empty or not.

 

So I tried to add Record.RemoveFields() to the formula according to below, but that didn't work:

= Table.SelectRows(#"Added Index", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,"Index")), {"", null})))

 

Any Idea how to remove empty rows based on the values in e.g. columns C1, C2, C3, but not the values in C4 and C5

1 ACCEPTED SOLUTION
pade
Advocate III
Advocate III

I must have done a typo when I tried my own solution

 

Having a table (Table1) with columns C1, C2, C3, C4, C5. And you would like filter out the none empty rows based on columns C1, C2, C3, you can use the formula below:

 

= Table.SelectRows(#"Table1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"C4", "C5"})), {"", null})))

 

Fun to sometimes be able to answer your own questions...

View solution in original post

1 REPLY 1
pade
Advocate III
Advocate III

I must have done a typo when I tried my own solution

 

Having a table (Table1) with columns C1, C2, C3, C4, C5. And you would like filter out the none empty rows based on columns C1, C2, C3, you can use the formula below:

 

= Table.SelectRows(#"Table1", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(Record.RemoveFields(_,{"C4", "C5"})), {"", null})))

 

Fun to sometimes be able to answer your own questions...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.