Same column str detection and

[Melissa]

Hi @kurzashane,

There’s a bit to it but Power Query is up to the task.

1. Created a Table.Buffer step to load the Sorted table into memory.
   

   

2. Created a nested table containing only the rows for the Current Job Requisition
   

   

3. Added a Custom Column with the required logic

      if Text.Contains([#"Freeze/Unfreeze"], "Freeze") and [Job Approval date] < [#"Freeze/Unfreeze date"]      
         then List.Count(
            List.Select(
              List.Dates(
                Record.Field(Table.Min([TempTable], "Freeze/Unfreeze date"), "Freeze/Unfreeze date"), 
                Number.From(
                  Record.Field(Table.Max([TempTable], "Freeze/Unfreeze date"), "Freeze/Unfreeze date")
                ) - Number.From(
                  Record.Field(Table.Min([TempTable], "Freeze/Unfreeze date"), "Freeze/Unfreeze date")
                ) + 1, 
                Duration.From(1)
              ), 
              each Date.DayOfWeek(_, 1) < 5
            )
          )
          else 0
   

.
For other posts, containing parts of this Power Query technique see:

Identify non duplicates, to remove, and then calculate time spend on two steps, and average as well

Ok so I’ve been exploring ways to avoid the second Group By operation and succeded So would you be so kind to test these variatons as well and let me know if performance improved? Thanks @Jetuke_ES Now for future reference, this is what I’ve done: [image] [image] [image] Next I extracted the Min value for [Created On] in two different ways: [image] Did the same for the Max value for [Completed on] also two approaches: [image] Here are the updates files. I hope this is helpf…

Virtual Table of Weekdays between two date columns

Okay so give this a try. Create a New from Blank Query and call it MinDate [image] Create another called MaxDate [image] Change the Custom Column M code to [image] let LBound = if [FromDate] < MinDate then MinDate else [FromDate], UBound = if [ToDate] > MaxDate then MaxDate else [ToDate] in List.Select( List.Difference( List.Dates( LBound, Number.From( UBound - LBound ) +1, Duration.From(1)), List.Transform(#"Holiday Table"[Holiday Date], Date…

.
I hope this is helpful. Here’s my sample file.
PQ working days job logic.pbix (29.8 KB)