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), ...
Need Help
  • 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

Icey's avatar
Icey
Community Support
5 years ago

Hi lukasjar ,

 

Try this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\iceyzh\Downloads\lexit data.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TrDt", type datetime}, {"TrmID", Int64.Type}, {"ProdID", Int64.Type}, {"OrdID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TrmID", "ProdID", "OrdID"}, {{"All", each _, type table [TrDt=nullable datetime, TrmID=nullable number, ProdID=nullable number, OrdID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([All],"Index",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"TrDt", "TrmID", "ProdID", "OrdID", "Index"}, {"TrDt", "TrmID", "ProdID", "OrdID", "Index"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom", "Index.1", 1, 1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "NextTrDt", each let trmID = [TrmID], prodID = [ProdID], ordID = [OrdID], index = [Index], index1=[Index.1],
tab1 = Table.SelectRows(#"Added Index",each [TrmID]=trmID and [ProdID] = prodID and [OrdID] = ordID and [Index]=index+1),
tab2 = Table.SelectRows(#"Added Index",each [Index.1]=index1+1)
in 
if Table.IsEmpty(tab1) then
Table.Max(tab2,"Index")[TrDt]
else
Table.Max(tab1,"Index")[TrDt]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Diff", each [TrDt] - [NextTrDt]),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom2", each [Index.1] >= 975 and [Index.1] <= 990),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Index", "Index.1", "NextTrDt"})
in
    #"Removed Columns"

 

BTW, .pbix file attached.

 

Best regards

Icey

 

If this post helps, then consider Accepting it as the solution to help other members find it faster.

lukasjar's avatar
lukasjar
Resolver I
5 years ago

This seems to be working, however when doing this to all rows the if statements took forever to load so i think the calc function that Anonymous suggested must be the solution to go with, since I have millions of rows to manage.