Same column str detection and

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

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:


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

1 Like

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!