I was recently working with a dataset and found that there were a lot of columns that were completely null, now going 1 by 1 through each column checking if everything in that column is null was a tedious task, so I decided let's write some custom M code that will help in getting done with removing those columns.
The data we are going to work on:
The first thing to do is to add a step that will extract profiling information of the above table, we need to use Table.Profile function for this.
Table.Profile will give us some summary statistics of each column, and we are going to use this information to filter out the null columns.
Next we are going to add a custom column and use if statement to check if value in current row of [NullCount] column is equal to the total rows of the Source(original table)
This custom column helps in identifying the rows to keep and remove.
Next, we will filter the rows where the value is equal to "Keep Rows"
This will leave us with 3 rows, which represents the columns that are not completly null.
Next we need to keep only the first column, which we will later use to select columns.
Lets use Drill Down or RemovedOtherColumns[Column] to convert the table into a list.
Drill down converts the table into a list of values, I have named this step as ColumnToKeep
Now we need to use Table.SelectColumns to select column from the Source step and only select the columns that have a matching value in the list shown in the previous image.
And just like that we have removed all the null columns from the data without manully selecting each column.
If you want to try out yourself, just paste this code in advanced editor of a blank query:
Hi this is very helpful, however the new table is now sorted by alphabetical columns? Any way to preserve the original order?