cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pedroccamara
Helper II
Helper II

Delete records or rows if blank field

Hello everyone

I use to work a file that has like 10.000 rows.

The "clean" step is very important cose i may end with just 8.000 records.

My question is: How to delete rows or records, if on certain field, column, has null value. I want to delete them, or learn how to do it, instead of hidden them. Is it possible?

Tks in advance

Pedro

1 ACCEPTED SOLUTION

Hi @pedroccamara - welcome back!

 

When you filter values out of a column in the query editor, it's essentially the same as deleting them. Those values will not be loaded to your data model, which will have the benefits of decreasing your load time and having fewer rows to process your calculations against.

View solution in original post

7 REPLIES 7
KGrice
Solution Sage
Solution Sage

Hi @pedroccamara. While you're still in the Query Editor (where you get data, before you close and apply), select the column you want to use to check for nulls. If the values you're checking are actually blank, you can click the drop-down for Remove Rows in the ribbon, then click Remove Blank Rows.

 

If the rows are null, you can use the filter drop-down in the column and select Remove Empty.

 

 

Hello @KGrice

Many thanks for your answer.

I don't think it's working cose it deletes just the blancks. Power bi, for all it was blanks, add a "null value". I know they're blanks but power bi put them as null. Can i delete all rows that has null value on that field?

@pedroccamara

 

You should be able to use the second option I listed. I used the same thing to eliminate all rows where the column contained the word null as a null value.

Hello KGrice

I'm sorry i didn't reply sooner but i was in vacations.

The answer you gave me, i think, it's not what i want, cose it doesn't delete them, but only hide it.

Not sure if it is the same thing....

Hi @pedroccamara - welcome back!

 

When you filter values out of a column in the query editor, it's essentially the same as deleting them. Those values will not be loaded to your data model, which will have the benefits of decreasing your load time and having fewer rows to process your calculations against.

View solution in original post

Not correct.when u do like that the other columns data also will lose.

 

Ex :

column1 having 100 records

column2 having 50 records and rest of the columns are contains null values

 

Suppose  if you filter values of Null of the column2 the other column1 data records also will get effected and leads to lost the data. column1 will have only 50 records.

 

If i m wrong kindly update .

 

Thanks

Hari

 

That i didn't know, hide is the same as delete. 

Thank you for the explanation KGrice.

Best regards

Pedro

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors