CODEWITHSUNDEEP
excelpowerbipowerquerypowerbi-desktop
James Cook
James Cook

Reputation: 344

Power Query null issues

I am trying to create a custom column in power query that counts null values from column 7 - column 41. The function I have used is List.NonNullCount(List.Range(Record.FieldValues(),7,41))). There are many many rows of data so I would like it to calculate each row within the custom column. For some reason even though each column between 7-41 has null values across the row the output is 2. I would expect the output to be 0. Futhermore I am getting an output of 35 when all columns from 7-41 have a value not equal to null across the row. I would expect 34 in this case. I did replace all blank values with null using the transform function in power query. I'm not sure if my function List.NonNullCount(List.Range(Record.FieldValues(),7,41))) is correct or if there is a better way to do it. any help appreciated!!!!

Upvotes: 0

Views: 764

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60484

In my version, you need to have an argument for Record.FieldValues. What you write: Record.FieldValues() would produce an error.

Also, the third argument of List.Range is the number of columns to return; it is not the number of the last column.

And the first column is column 0, not column 1

So something like:

List.NonNullCount(List.Range(Record.FieldValues(_),6,35))

should return the nonNull count for columns 7-41

Upvotes: 1

Related Questions