Power query - List.NonNullCount with dynamic number of columns

0
votes

in power query I want to add a column counting the empty cells per row. For this I use

Table.AddColumn(#"Previous Step", "Count", each List.NonNullCount({[Column1], [Column2]}))

This works fine.

But now I have the problem that due to previous input I can have N Columns (with different names) The columns names I have available as extra list / table already.

When I try to use the list within List.NonNullCount the result is the same for all rows and therfore wrong

How to correctly hand over the column names to List.NonNullCount({[Column1], [Column2]}) ?

1
powerquery

1 Answers

0
votes

This code should work:

= Table.AddColumn(PreviousStep, "cnt", each List.NonNullCount(Record.ToList(Record.SelectFields(_, YourListOfColumns))))

If you need to count values in all columns you may use Table.ColumnNames(PreviousStep) instead of YourListOfColumns.