Forum Discussion
M Code to remove blanks and nulls from rows in a single sweep
Here are all the transforms, including the changed types and column names. This replaces the entirety of your function (minus the removal of duplicates, but that's easy to add).
h/t to Mr. von Neumann
My original question.
Here is standard code to remove blanks for a single column, in this case "Item Description".
#"Filtered Rows" = Table.SelectRows(#ReplaceNull, each [Item Description] <> null and [Item Description] <> "")
I can copy that statement for each column I want to remove nulls, but that's (say) 3 passes through the large data set. Is there a way I can get a list for all the columns I want to test for blanks and have them removed on a single pass? Any solution I've tried, passing a list of the columns to the Table.SelectRows or similar function and/or using the "_" operator, creates an AND function which means all the columns have to be blank in order for the row to be removed. I want the row to be removed if any column meets that condition, and to do so in a single pass.
- lbendlin
Super User
I think that should be possible. Please provide a small sample that covers the issue, and indicate the expected outcome based on that sample.
Some things to ponder: Table.ColumnNames() and List.Contains()
I have created a small sample file, but I'm failing the IQ test on how to upload a spreadsheet to this forum. Try this link to pull a sample data file and the spreadsheet to run the query functions.
Based on the settings in the "Remove Empty" column I want fnSetMetaDataFromTable to remove both rows 4 and 6.
- lbendlin
Nearly there - can you please make the OneDrive link not ask for authentication?