Please follow this process step by step you will get the desired result

Power Query – Duration Calculation Between Item A and Item B

This document explains how to calculate the time difference (Duration) between Item A and Item B using Power Query in Power BI.

Scenario

Solution Steps

1. Open Power Query Editor in Power BI Desktop.
2. Ensure the 'Time' column data type is set to Time.
3. Add an Index Column (Add Column → Index Column → From 1).
4. Duplicate the query and create two queries: A_Table and B_Table.
5. Filter A_Table where Item = 'A' and B_Table where Item = 'B'.
6. Merge A_Table with B_Table using the Index column (Left Join).
7. Expand the Time column from B_Table.
8. Add a Custom Column using the formula: [Time] - [B_Table.Time].
9. Set the new column data type to Duration.

Example Output

  
Git Hub File Reference, you can download that file with the solution.
https://raw.githubusercontent.com/Murtaza-007-007/PBI-Fabric-Community/9d12a747d505383cd3cb43a65af225122772291e/Calculate%20Duration%20Using%20Power%20Query.pbix

If this helped, ✓ Mark as Solution | Kudos appreciated

Hi marccxx ,

When you refer that you want to calculate the duration is it the sum of the time between the current line and previous line so for this case second line 5 minutes third line 7 minutes?

Or do you want to have a different way to calculate the duration?

Depending on your needs there is the duration functions that allow you to do that calculation:

https://learn.microsoft.com/en-us/powerquery-m/duration-functions

Hi Felix,

leider wird nicht ganz klar was Du genau berechnen willst. Die Dauer zwischen den Vorgängerzeilen oder Indexabhängig zwischen A und B? 

Für den 1. Fall kannst Du so vorgehen:

Für den 2. Fall wäre das möglich:

let
    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle17"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Time", type time}}),
    #"Sortierte Zeilen" = Table.Sort(#"Geänderter Typ",{{"Time", Order.Ascending}}),
    Start = Table.SelectRows(#"Sortierte Zeilen", each ([Title] = "a")),
    #"Hinzugefügter Index" = Table.AddIndexColumn(Start, "Index", 0, 1, Int64.Type),
    Ende = Table.SelectRows(#"Sortierte Zeilen", each ([Title] = "b")),
    #"Hinzugefügter Index1" = Table.AddIndexColumn(Ende, "Index", 0, 1, Int64.Type),
    #"Zusammengeführte Abfragen" = Table.NestedJoin(#"Hinzugefügter Index", {"Index"}, #"Hinzugefügter Index1", {"Index"}, "Hinzugefügter Index1", JoinKind.LeftOuter),
    #"Erweiterte Hinzugefügter Index1" = Table.ExpandTableColumn(#"Zusammengeführte Abfragen", "Hinzugefügter Index1", {"Time"}, {"Ende_B"}),
    #"Entfernte Spalten" = Table.RemoveColumns(#"Erweiterte Hinzugefügter Index1",{"Index"}),
    #"Umbenannte Spalten" = Table.RenameColumns(#"Entfernte Spalten",{{"Time", "Start_A" }}),
    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(#"Umbenannte Spalten", "Dauer", each [Ende_B]-[Start_A], type duration)
in
    #"Hinzugefügte benutzerdefinierte Spalte"

Hi marccxx ,

I would also take a moment to thank Murtaza_Ghafoor  , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions

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