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

     

ralf_anton's avatar
ralf_anton
Resolver I
6 months ago

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"