Now, the blog was inactive for over two years, but it is coming back to life! Lots of changes on the personal side (moving to a new country, a new language and a baby, among other things). Many times I was about to post something and then life stopped me.
The problem
There was a question on the global Power BI Facebook group about counting (non)empty columns in Power Query. I thought it’s a good mix of basic and advanced knowledge for Power Query, so here’s the post.
The solution
How can we do that, without specifying the name of each column?
The answer lies in the Record object in Power Query – and by definition, each line is a record of its own, which can be called with a simple underscore: _ (see more about that in the post of Lars Schreiber here: https://ssbi-blog.de/blog/technical-topics-english/records-in-power-query-how-when-and-why/).
So the approach is – build a formula that counts all non-blank items in the current row. Now that we now how to call the current row, we need the function List.NonNullCount which does what it says it does. You might ask – where is this list coming from? Well, that’s the next piece – we need to transform the Record to a List. For this purpose, we use the conveniently called function Record.ToList.
So, if we we combine these, we have
List.NonNullCount(Record.ToList(_) ) – the underscore is the current row, converted to a list by the function. The non-null items will be counted by the outermost function.
What if… The null values are not nulls, but empty instead.
Mathematical logic tells us to bring the problem to the last known solution and apply minimum changes – we can do that via List.ReplaceValue – this function will allow us to turn the empty cells to null. The final expression looks like this:
List.NonNullCount(
List.ReplaceValue(
Record.ToList(_), “”, null, Replacer.ReplaceValue))
Vitali
*Yes, that’s a reference to the Pink Floyd song. Here’s your prize: https://www.youtube.com/watch?v=KrRspHOyWTo
Vitali on Data 