If all the items for a particular column are empty, I want to remove that column from the DataTable. What's the most elegant way to do this operation on all columns in the DataTable?
In the Home tab, click on Transform data. In Power Query Editor, select the query of the table with the blank rows and columns. In Home tab, click Remove Rows, then click Remove Blank Rows. To repeat the same process on the empty columns, we can transpose the table, because we don't have Remove Blank Columns in the UI.
To remove observations with missing values in at least one column, you can use the na. omit() function. The na. omit() function in the R language inspects all columns from a data frame and drops rows that have NA's in one or more columns.
You can use the Compute
method, like this:
if (table.Compute("COUNT(ColumnName)", "ColumnName <> NULL") == 0)
table.Columns.Remove("ColumnName");
Alternatively, you can use LINQ:
if (table.AsEnumerable().All(dr => dr.IsNull("ColumnName")))
table.Columns.Remove("ColumnName");
EDIT: To completely answer the question:
foreach(var column in table.Columns.Cast<DataColumn>().ToArray()) {
if (table.AsEnumerable().All(dr => dr.IsNull(column)))
table.Columns.Remove(column);
}
You need to call ToArray
because the loop will modify the collection.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With