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

[RICH937]

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?

  #"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"}),

 

[Matt Allington]

Why didn’t “remove blanks” work? Make sure you select the single column first

 

[RICH937]

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?

 

[Matt Allington]

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

 

[RICH937]

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.

 

[Matt Allington]

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.