What is the right way to remove blank rows in power query

RICH937

Board Regular
Joined
Apr 15, 2023
Messages
59
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Hi folks. My data set has a few columns that are used to identify subsegments of other columns. For example, the "government" segment column has multiple subsegments like Army, Navy, Airforce, State Dept, etc. in another column. In the subsegment column, those segments that don't have subs are blank (blank rows in attached screenshot). Some of the analysis I am doing is on these subsegments, and I was having a hard time figuring out how to limit the data in a query to just those rows with subsegment data. I thought "remove blank rows" would work, but it did not. My work around was to do the following. I'm going to write a summary of what I did, just in case the M quick wrap doesn't work. First time using it. SUMMARY: I added a custom column named "Remover" and used a conditional on the segment (category) column that either resulted in the value from the subsegment [analyzed] column or the value from another column [madeup]. The second column did not exist, so an error was generated. I could then use the remove errors function to delete the rows that did not have a value in the subsegment column, and then delete the custom column "remover."

This works. However, I'm sure it's not the right way. Can someone tell me the correct way to go about this?


Power Query:
  #"Added Custom" = Table.AddColumn(
    REMOVE_Unused_COLUMNS,
    "REMOVER",
    each if [ANALYZED] <> "" then [ANALYZED] else [MADEUP]
  ),
  #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"REMOVER"}),
  #"Removed Columns" = Table.RemoveColumns(#"Removed Errors", {"REMOVER"}),
 

Attachments

  • gov subs.PNG
    gov subs.PNG
    7.3 KB · Views: 16

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Why didn’t “remove blanks” work? Make sure you select the single column first
No idea. It's the first column so I thought I might not have actually selected it as it was green when I opened the query. Did it 3x and on 3 different columns with empty cells. My assumption was that for blank rows to work, all the fields in that row (not just column) had to be blank?
 
Upvote 0
No, all columns do not need to be blank. Filtering only occurs on the columns selected. I suggest you click into one of the "blank" cells. A window will appear down the bottom of the screen showing the actual cell contents. This window is selectable, so you can actually select, cut and paste the text. Maybe it's not blank but instead contains spaces. I think you can also right click the cell contents and use the shortcut menu to filter that cell/row out. Give it a try
 
Upvote 0
No, all columns do not need to be blank. Filtering only occurs on the columns selected. I suggest you click into one of the "blank" cells. A window will appear down the bottom of the screen showing the actual cell contents. This window is selectable, so you can actually select, cut and paste the text. Maybe it's not blank but instead contains spaces. I think you can also right click the cell contents and use the shortcut menu to filter that cell/row out. Give it a try
Not sure how I missed the notification that you had responded, but apologies for the late reply. I've gotten rid of them all now using the create errors technique, but I was curious if there was actually content in the cells so I went back to the original data these query columns are based on. They're all products of excel filtering one table's values in another. In the excel filter formulas, the input for "If_empty" are all "" with no space between the quotes so I don't think that's it. Just to double check, I redid the query and recorded the screen so you could see what I'm working with. It's loaded to my YT page. Here's the link. YouTube Vid. I can't figure out why it's not working, but the workaround I described above seems to function just fine.

I found your reply when I came to ask for what I think a super simple exists for translating a common Excel formula to PQE formula. If you have time to look at that one, I'd be super appreciative. I've spent hours and hours trying to figure it out, and I simply cannot.
 
Upvote 0
From what I can see, there are empty strings and possibly blanks, too. The easiest way to learn is to right click on one of the cells and then us the menu to filter them out. Then you can check the code to see what code the UI used to perform the task. If you do this repeatedly you will learn the different presentations of an apparent blank cell.
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top