Transpose table with calculation

Hi,Greg_Deckler ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hello,CornelisV . I am glad to help you.

Like this?

This is my test data:
I'm assuming that you have multiple records like this in your real data with multiple dates
 

You can add a grouped sorting sequence for calculations (based on the value of index).
This way it is easier to locate and calculate when multiple records exist at the same time.

I have created a grouping sequence (assuming that each day exists and is grouped by date)

Create the required Duration (calculated based on whether the GroupID is an odd number (End), versus the previous column of data (Start))

Filtering to remove Null rows
Getting results

Note that Power BI Desktop does not support the Duration type of data, which is displayed as a Text/Decimal number by default.

So I recalculated the column [Totals Minutes], calculated the duration of each row (in minutes)

The results in Power BI Desktop are as follows:

This is my M code:

let
    Source = Excel.Workbook(File.Contents("C:\Users\username\Desktop\test_11_11.xlsx"), null, true),
    StatesTable_Sheet = Source{[Item="StatesTable",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(StatesTable_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Time", type time}, {"Phase", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Date"}, {{"DailyGroupID", each Table.AddIndexColumn(_, "Index", 0, 1, Int64.Type)}}),
    #"Expanded DailyGroupID" = Table.ExpandTableColumn(#"Grouped Rows", "DailyGroupID", {"Time", "Phase", "Index"}, {"DailyGroupID.Time", "DailyGroupID.Phase", "DailyGroupID.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded DailyGroupID",{{"DailyGroupID.Time", "Time"}, {"DailyGroupID.Phase", "Phase"}, {"DailyGroupID.Index", "GroupIndex"}}),
    CalculateDuration = Table.AddColumn(#"Renamed Columns", "Duration", each if Number.Mod([GroupIndex], 2) = 1 then Duration.From([Time] - #"Renamed Columns"[Time]{[GroupIndex]-1}) else null),
    FilteredRows = Table.SelectRows(CalculateDuration, each [Duration] <> null),
    ResultTable = Table.SelectColumns(FilteredRows, {"Duration", "Phase","Date"}),
    #"Replaced Value" = Table.ReplaceValue(ResultTable,"End heating","Heating",Replacer.ReplaceText,{"Phase"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","End cooling","Cooling",Replacer.ReplaceText,{"Phase"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value1",{{"Duration", type duration}}),
    #"Inserted Total Minutes" = Table.AddColumn(#"Changed Type1", "Total Minutes", each Duration.TotalMinutes([Duration]), type number)
in
    #"Inserted Total Minutes"

I shared the pbix file used for the test

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.