Forum Discussion
count when column is filled in
I have a table with several dates column to be filled in.
| Batch | 1 dilevery date | 1 dilevery date | 3 dilevery date | 4 dilevery date |
| A | 2024.09.02 | |||
| B | 2024.12.02 | 2025.04.02 | 2025.07.02 | 2025.09.02 |
| C | 2024.10.02 | |||
| D | 2024.03.02 | 2024.04.02 | 2024.05.02 |
I would like to create a new column (using tranformation data) to count the number of columns filled in i.e. the number of deliveries.
For batch A is 1; batch B is 4; batch C is 1; batch D is 3
Hi jps_HHH - you can create it using a function List.functon as follows in to get the delivery date columns
In the Power Query Editor, click on "Add Column" in the ribbon.Select "Custom Column" from the dropdown menu.
List.NonNullCount({[1 dilevery date],[2 dilevery date],[3 dilevery date],[4 dilevery date]})
output:
Hope it works
2 Replies
Hi jps_HHH - you can create it using a function List.functon as follows in to get the delivery date columns
In the Power Query Editor, click on "Add Column" in the ribbon.Select "Custom Column" from the dropdown menu.
List.NonNullCount({[1 dilevery date],[2 dilevery date],[3 dilevery date],[4 dilevery date]})
output:
Hope it works
Are there always 4 date columns?
If so something like this should do the trick:
= Table.AddColumn(#"Changed Type1", "Count", each List.NonNullCount({[1 dilevery date_1], [3 dilevery date], [4 dilevery date]}), Int64.Type)
