Count columns that are non-null, non-blank, non-zero

[lopez235]

I have survey data I work with that typically has about 50 columns or more. In the past, in Excel, I would add a column with the formula countifs(rng1,"<>",rng1,"<>0") and call this "Answered" (abrev: "Answ'd") as in how many items did the respondent answer. It feels like there should be a pretty efficient way of doing this in Power Query. I want to figure out the most efficient way of doing this in PQ because there are other transformation steps I perform prior to loading to the data model. I already figured out a couple of options. 1) I can reference my query then pivot/unpivot and perform a count and then merge both queries. But that seemed like one step too many and in the past I have sometimes experienced PQ refresh performance issues, depending on the size and source of my data. 2) I can replace all blanks and 0 values with nulls and then add a column this way (list.range used to exclude the first few columns):

= Table.AddColumn(#"Replaced Value2", "Answ'd", each List.NonNullCount(List.Range(Record.FieldValues(_),3)))

I'm guessing #2 is the most efficient way but curious if others have an even better way of doing this. I was envisioning meeting the requirement of counting all columns that are non-null, non-blank, non-zero with a single line of code (and that excludes the first few columns as above). Is that possible?

[AlexisOlson · Accepted solution]

Personally, I would try the unpivot route first and try other methods if that didn't work efficiently.

If you go with a custom column, I'd do this to avoid the extra replacement step:

List.Count(List.Difference(List.Range(Record.FieldValues(_),3), {null, "", 0}))

[AlexisOlson · Accepted solution]

Personally, I would try the unpivot route first and try other methods if that didn't work efficiently.

If you go with a custom column, I'd do this to avoid the extra replacement step:

List.Count(List.Difference(List.Range(Record.FieldValues(_),3), {null, "", 0}))

[lopez235 · Accepted solution]

My apologies, took me a while to come back and fully test (we were short-staffed/recent turnover).  The solution you gave me was almost there. It wasn't counting correctly when it came across rows with nulls in all columns. But this is what I modified it to (added List.RemoveNulls).

= Table.AddColumn(#"Changed Type", "NullBlankSpaceZero", each List.Count(List.RemoveNulls(List.Difference(List.Range(Record.FieldValues(_),2), {null, "", " ", 0}))))

note: there are some small differences from what I initially gave as an example that's why I adjusted field values and list diff compare list.

Thanks again! your reply set me on the right path