Same column str detection and

[kurzashane]

Hello all,

I’ve been struggling to create these calculated columns in Power BI. Columns F,G,H contain a formula which calculates the time a job has been “frozen”. When the same job has been froze/unfroze they are grouped together as in the example. The formula checks to see if the word “freeze” is contained in the cell and then makes sure “Job 1” is the same in both A2 & A3. It then looks to D2 to see if it is < than I2, if it is than it gets the working days from (D3 - D2).

Has anyone encountered a problem like this? My initial thought was to try and manipulate the data in power query so all the “req ID’s” are one line? Any help is appreciated

[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)

[EnterpriseDNA]

Hi @kurzashane, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!