Forum Discussion

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

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

lukasjar

Resolver I

5 years ago

Hello.

Yes I have the datetime column, I have added it myself.

Can it be a issue that I try grouping by a unique number (TrID and DateTime is uniqe), causing the function not to work? I also removed the double grouping but it did no difference.

Anonymous

Not applicable

5 years ago

If you upload a complete excel I can try to rewrite/adapt the code.

If you provide also info about struct and dimensional aspect I can try to write a code to better fits your struct

lukasjar
Resolver I
5 years ago
I have uploaded them to dropbox, "lexit result 2" and "lexit data 2".

files for: lexit result 2, lexit data 2

Anonymous

Not applicable

5 years ago

let


    calcCycle=(tab)=>
    let
    n=Table.RowCount(tab),
    cols=Table.ColumnNames(tab),
    tabs=Table.Sort(tab,{"Time", Order.Descending}),
    cyb=List.Buffer(tabs[Time]), 
    cyTime=List.Accumulate({1..n-1}, {}, (s,c)=>s&{cyb{c-1}-cyb{c}})&{null},
    out=Table.FromColumns(Table.ToColumns(tabs)  & {cyTime}, cols & {"CycleTime"})
    in out,

    Origine = Excel.Workbook(File.Contents("C:\Users\37332115\OneDrive - TIM\MyD2020\BI\lexit data 2.xlsx"), null, true),
    Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Intestazioni alzate di livello" = Table.PromoteHeaders(Sheet2_Sheet, [PromoteAllScalars=true]),
    #"Modificato tipo" = Table.TransformColumnTypes(#"Intestazioni alzate di livello",{{"TrDt", type datetime}}),
    #"Aggiunta colonna personalizzata" = Table.AddColumn(#"Modificato tipo", "Date", each Date.From([TrDt] ), type date),
    #"Aggiunta colonna personalizzata1" = Table.AddColumn(#"Aggiunta colonna personalizzata", "Time", each Time.From([TrDt]), type time),
    #"Raggruppate righe" = Table.Group(#"Aggiunta colonna personalizzata1", {"Date", "TrmID", "OrdID"}, {{"CycleT", each calcCycle(_)},{"Size", each Table.RowCount(_)}}),
    #"Tabella CycleT espansa" = Table.ExpandTableColumn(#"Raggruppate righe", "CycleT", {"TrID", "ProdID", "ProdNm", "UsrID", "Batch", "Time", "CycleTime"}, {"TrID", "ProdID", "ProdNm", "UsrID", "Batch", "Time", "CycleTime"})
in
    #"Tabella CycleT espansa"

try this gradually :-).

First use 100k rows dataset then calc the time (the inrease should be linear) and multiply for yourdatasetSize/100k.