Pairing start and end dates by date order

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

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

Resulting in...