Forum Discussion

lukasjar's avatar
lukasjar
Resolver I
5 years ago
Solved

Calculate time difference to previous matching row using If statements

Hello. I am trying to calculate the time difference between rows according to below rules.   if(TrmID=Previous(TrmID) and Date(floor(TrDt))=previous(Date(floor(TrDt))) and OrdID=Previous(OrdID), ...
  • Anonymous's avatar
    Anonymous
    5 years ago

     

    let
        Origine = Excel.Workbook(File.Contents("C:\Users\37332115\OneDrive - TIM\MyD2020\BI\lexit data.xlsx"), null, true),
        Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data],
    
        calcCycle=(tab)=>
        let
        n=Table.RowCount(tab),
        cyb=List.Buffer(tab[Time]), 
        cyTime=List.Accumulate({1..n-1}, {}, (s,c)=>s&{cyb{c-1}-cyb{c}})&{null}
        in cyTime,
        #"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
        #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TrDt", type datetime}, {"TrmID", Int64.Type}, {"ProdID", Int64.Type}, {"OrdID", Int64.Type}}),
        #"Suddividi colonna in base al delimitatore" = Table.SplitColumn(Table.TransformColumnTypes(#"Modificato tipo", {{"TrDt", type text}}, "it-IT"), "TrDt", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"TrDt.1", "TrDt.2"}),
        #"Modificato tipo1" = Table.TransformColumnTypes(#"Suddividi colonna in base al delimitatore",{{"TrDt.1", type date}, {"TrDt.2", type time}}),
        #"Rinominate colonne" = Table.RenameColumns(#"Modificato tipo1",{{"TrDt.1", "Date"}, {"TrDt.2", "Time"}}),
        #"Raggruppate righe" = Table.Group(#"Rinominate colonne", {"Date", "TrmID", "OrdID"}, {{"CycleTime", each calcCycle(_)}}),
        #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Raggruppate righe", "CycleTime"),
        #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}})
    in
        #"Modificato tipo2"

     

     

    just to make the code a little less (unnecessarily) redundant and perhaps more efficient, since the table to which you have to apply it is not small