Filtering and counting items in an aggregated list

I have a dataset that includes a start date and end date for a given process.

What I need to work out, is how many working days there are between each date (referencing a list of holidays and working out if a date is a weekend or not)

I know how I could do this in a loop, but the volume of data means that this takes far too much time.

This ultimately is to replicate the List.Contains function in Power Query.

My thought is, if I use the Groupby node to create a list of all the working days across a suitable period of time, I could then cross join this to my dataset, then somehow filter each list based on the start and end date in that row, then count how many items left in the list, without having to resort to a loop.

Any ideas would be greatly appreciated.

If i am recalling this correctly this problem was approached here

Hi @knightyboy

There is a node by @Vernalis that returns the length of collections:

It works for Collections, Lists, Arrays, bitArrays, ByteArrays, etc.

It is the simplest and most direct way to address the problem.

Hope this helps.



You can consider array functions available in

node. Say arrayContains().

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.