Forum Discussion
lukasjar
5 years agoResolver I
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), ...
- Anonymous5 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
Anonymous
5 years agoNot applicable
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