Forum Discussion

marccxx's avatar
marccxx
New Member
6 months ago
Solved

how to add duration using power query

Hi! I am new to Power BI and I need help for problem. I have a data that looks like this:     I want to make a column named Duration where it calculates the time for Title[A] and Title[B], o...
Need Help
Tutorial Requests
  • Kateryna_dev's avatar
    Kateryna_dev
    6 months ago

    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

    TitleTime
    A1:30 PM
    A1:35 PM
    A1:37 PM
    B12:30 PM
    B12:45 PM
    B12:46 PM

     

    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

     

  • Kateryna_dev's avatar
    Kateryna_dev
    6 months ago

    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

    TitleTime
    A1:30 PM
    A1:35 PM
    A1:37 PM
    B12:30 PM
    B12:45 PM
    B12:46 PM

     

    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

     

Murtaza_Ghafoor's avatar
Murtaza_Ghafoor
Super User
6 months ago

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

Item

Time

A

1:30 PM

A

9:15 AM

A

3:45 PM

B

11:20 AM

B

2:10 PM

B

4:55 PM

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

Item

Time (A)

Time (B)

Duration

A

1:30 PM

11:20 AM

02:10:00

A

9:15 AM

2:10 PM

-04:55:00

A

3:45 PM

4:55 PM

-01:10:00


  
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