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
datavis
Resolver I
Resolver I

Delete rows if blank field

I created two columns related to another data table. I need to delete rows with blanks. I go to query editor and I do not see the columns I created so I can filter out the rows with blanks in the new columns. How can I delete or filter out the rows with blans in those fields?

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @datavis

If you want to filter out blank row in report view, you can set "show item when the value is not blank" in Visual Filter container.

3.png

 

If you want to change the data model with no blank value from column related from other tables.

You could use the formula to create a new table.

table = FILTER(ALL(Sheet2),[Column]<>BLANK())

4.png

 

Best Regards

Maggie

View solution in original post

6 REPLIES 6
v-juanli-msft
Community Support
Community Support

Hi @datavis

If you want to filter out blank row in report view, you can set "show item when the value is not blank" in Visual Filter container.

3.png

 

If you want to change the data model with no blank value from column related from other tables.

You could use the formula to create a new table.

table = FILTER(ALL(Sheet2),[Column]<>BLANK())

4.png

 

Best Regards

Maggie

Yes! Thank you very helpful!

Anonymous
Not applicable

Just to mention - Power BI cant delete any record, and you may aware of this. Basically, you wanted to filter the blank records.

As @parry2k said, please check where you have created the new columns  , is it done in DAX or in Power Query.

 

If its in DAX- you may need to use ISBLANK() function to filter the records.

 

 

Thanks

Raj

parry2k
Super User
Super User

Did you created the column using DAX ? if yes then you cannot see it in query editor. You can filter the records in query editor so that it doesn;t bring those blank rows to data model, let me know if need further assistance.

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you,

 

I clicked Modeling/New Column then named the column and used RELATED(Dept[Department]). So how do I filter it if I can't see the column in the data when I go to Query Editor? Sorry I am new to analytics and to Power BI.

 

 

 

In that case you can drop this field on page level filter and choose "is not blank", to understand various filters, read this article



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.