how to add duration using power query

You can also use M code to calculate duration between A and B, no need for Duration column -> it can be added altogether via code

Excel Data

Manual steps to perfomr -> Go to Power Query and set type data

From the comment split A and B into separate tables is the code to create 2 tables, clean up data and calculate duration.

let

   // this is your source data from Excel
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],


    // set correct data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Title", type text}}),



    // split A and B into separate tables
    table_a = Table.SelectRows(#"Changed Type", each [Title] = "A"),
    table_b = Table.SelectRows(#"Changed Type", each [Title] = "B"),

    // add Index column
    table_a_index = Table.AddIndexColumn(table_a, "Index", 0, 1, Int64.Type),
    table_b_index = Table.AddIndexColumn(table_b, "Index", 0, 1, Int64.Type),
    merge_ab = Table.NestedJoin(table_a_index, "Index", table_b_index, "Index", "b_table", JoinKind.Inner),
    expand_b = Table.ExpandTableColumn(merge_ab, "b_table", {"Time"}, {"b_time"}),

    // remove unnecessary columns and rename it
    rename_column_a = Table.RenameColumns(expand_b, {{"Time", "a_time"}}),
    remove_columns = Table.RemoveColumns(rename_column_a, {"Title", "Index"}),

    // calculate duration
    add_duration = Table.AddColumn(remove_columns, "Duration",
        each
            if [b_time] < [a_time] then [b_time] + #duration(1,0,0,0) - [a_time]
            else [b_time] - [a_time],
            type duration
    
    )
in
    add_duration

The outcome is the following

If you find this helpful, Kudos are appreciated

You can also use M code to calculate duration between A and B, no need for Duration column -> it can be added altogether via code

Excel Data

Manual steps to perfomr -> Go to Power Query and set type data

From the comment split A and B into separate tables is the code to create 2 tables, clean up data and calculate duration.

let

   // this is your source data from Excel
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],


    // set correct data type
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Time", type time}, {"Title", type text}}),



    // split A and B into separate tables
    table_a = Table.SelectRows(#"Changed Type", each [Title] = "A"),
    table_b = Table.SelectRows(#"Changed Type", each [Title] = "B"),

    // add Index column
    table_a_index = Table.AddIndexColumn(table_a, "Index", 0, 1, Int64.Type),
    table_b_index = Table.AddIndexColumn(table_b, "Index", 0, 1, Int64.Type),
    merge_ab = Table.NestedJoin(table_a_index, "Index", table_b_index, "Index", "b_table", JoinKind.Inner),
    expand_b = Table.ExpandTableColumn(merge_ab, "b_table", {"Time"}, {"b_time"}),

    // remove unnecessary columns and rename it
    rename_column_a = Table.RenameColumns(expand_b, {{"Time", "a_time"}}),
    remove_columns = Table.RemoveColumns(rename_column_a, {"Title", "Index"}),

    // calculate duration
    add_duration = Table.AddColumn(remove_columns, "Duration",
        each
            if [b_time] < [a_time] then [b_time] + #duration(1,0,0,0) - [a_time]
            else [b_time] - [a_time],
            type duration
    
    )
in
    add_duration

The outcome is the following

If you find this helpful, Kudos are appreciated