Pairing start and end dates by date order

[MJDS]

Hi there,

*edited to tidy up example data*

The problem

I'm trying to analyse allocation times in a case management system.  In the system we have a user profile called Unallocated.  I'm using case history data to identify the periods of time that the data is in with the Unallocated user.

The data and where I've got so far

This is how the data for a single case looks like in the SQL Server database:

CaseID; CaseHistoryID; HistoryCreated; HistoryComment

222201111; 1234123; 30/01/2024; Case owner changed from Jane Smith to Unallocated

222201111; 1352456; 02/02/2024; Case owner changed from Unallocated to Bob Brown

222201111; 1423781; 25/02/2024; Case owner changed from Bob Brown to Unallocated

222201111; 1532891; 15/04/2024; Case owner changed from Unallocated to Jane Smith

At the moment, I have an SQL statement in the source:
SELECT CaseID, CaseHistoryID, HistoryComment
FROM database
WHERE HistoryComment LIKE '%Unallocated%';

I then split/extract the HistoryComment text and add conditional columns to return the start and end dates.

The data then looks like this:

CaseID; CaseHistoryID; HistoryCreated; History type; From; To; End date; Start date

222201111; 1234123; 30/01/2024; Case owner changed; Jane Smith; Unallocated; [value is null]; 30/01/2024

222201111; 1352456; 02/02/2024; Case owner changed; Unallocated; Bob Brown; 02/02/2024; [value is null]

222201111; 1423781; 25/02/2024; Case owner changed; Bob Brown; Unallocated; [value is null]; 25/02/2024

222201111; 1532891; 15/04/2024; Case owner changed; Unallocated; Jane Smith; 15/04/2024; [value is null]

What I need the data to look like

I need to pair up start and end dates by CaseID, based on their order.  There will often be multiple start and end date pairs or start dates without end dates.

CaseID; Start date; End Date

222201111; 30/01/2024; 02/02/2024

222201111; 25/02/2024; 15/04/2024

I then will then visualise the following metrics in PowerBI:

• Number of cases in Unallocated over time
• Length of average length of time in Unallocated

What I've tried so far

Referencing the Next Row in Power Query • My Online Training Hub

Events in progress – DAX Patterns

Thanks!

[PwerQueryKees · Accepted solution]

OK. I had some trouble with your test data.

• The CaseID's were mixed up. You first table does not match the second and there was only one line per CaseID
• Lots of extra spaces and some space were not actually space

After fixing, I used this:

I noticed that the Cases seem to go from unallocated to a person and then back again.
I assumed that is what you want, so I left it alone.

I have a solution for you to build on:

let
    Source = Excel.CurrentWorkbook(){[Name="Case_History"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CaseID", Int64.Type}, {"CaseHistoryID", Int64.Type}, {"HistoryCreated", type date}, {"HistoryComment", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"HistoryComment", each Text.AfterDelimiter(_, "from "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "HistoryComment", Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv), {"From", "To"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"From", Text.Trim, type text}, {"To", Text.Trim, type text}}),
    #"Added Previous" = Table.AddColumn(#"Trimmed Text", "Previous", each 
        let
            previous_rows = Table.SelectRows(#"Trimmed Text", (row) => row[CaseID] = [CaseID] and row[HistoryCreated] < [HistoryCreated]),
            last_from_previous_rows = Table.First(Table.Sort(previous_rows,{{"HistoryCreated", Order.Descending}}))
        in
            last_from_previous_rows
    ),
    #"Expanded Previous" = Table.ExpandRecordColumn(#"Added Previous", "Previous", {"HistoryCreated", "From", "To"}, {"Previous.HistoryCreated", "Previous.From", "Previous.To"})
in
    #"Expanded Previous"

Producing the table with added columns from the previous transition in the same case:

The magic happens in the #"Added Previous" step. It essentially finds all rows in the same case, where the HistoryCreated date is earlier, then sorts decending on the HistoryCreeated date and returns the first (most recent) record.

This should get you started. You now have all transitions with the data from the previous transition. So you only have to do selections and other logic to satisfy your use case requirements.

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[jgeddes · Accepted solution]

Here is a pbix file with another method that uses the grouping function to get your results.
Starting with...

Resulting in...

[MJDS]

Thanks, I will check this out too!

[MJDS]

Thanks Kees, I will try this and see if it works for me then report back on this thread.

[PwerQueryKees · Accepted solution]

OK. I had some trouble with your test data.

• The CaseID's were mixed up. You first table does not match the second and there was only one line per CaseID
• Lots of extra spaces and some space were not actually space

After fixing, I used this:

I noticed that the Cases seem to go from unallocated to a person and then back again.
I assumed that is what you want, so I left it alone.

I have a solution for you to build on:

let
    Source = Excel.CurrentWorkbook(){[Name="Case_History"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CaseID", Int64.Type}, {"CaseHistoryID", Int64.Type}, {"HistoryCreated", type date}, {"HistoryComment", type text}}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Changed Type", {{"HistoryComment", each Text.AfterDelimiter(_, "from "), type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Text After Delimiter", "HistoryComment", Splitter.SplitTextByDelimiter(" to ", QuoteStyle.Csv), {"From", "To"}),
    #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"From", Text.Trim, type text}, {"To", Text.Trim, type text}}),
    #"Added Previous" = Table.AddColumn(#"Trimmed Text", "Previous", each 
        let
            previous_rows = Table.SelectRows(#"Trimmed Text", (row) => row[CaseID] = [CaseID] and row[HistoryCreated] < [HistoryCreated]),
            last_from_previous_rows = Table.First(Table.Sort(previous_rows,{{"HistoryCreated", Order.Descending}}))
        in
            last_from_previous_rows
    ),
    #"Expanded Previous" = Table.ExpandRecordColumn(#"Added Previous", "Previous", {"HistoryCreated", "From", "To"}, {"Previous.HistoryCreated", "Previous.From", "Previous.To"})
in
    #"Expanded Previous"

Producing the table with added columns from the previous transition in the same case:

The magic happens in the #"Added Previous" step. It essentially finds all rows in the same case, where the HistoryCreated date is earlier, then sorts decending on the HistoryCreeated date and returns the first (most recent) record.

This should get you started. You now have all transitions with the data from the previous transition. So you only have to do selections and other logic to satisfy your use case requirements.

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel