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

lukasjar's avatar
lukasjar
Resolver I
5 years ago

You don't happen to have the code in english? ğŸ˜… I managed to get it working with the excel file I sent you but I must have missed adding the TrID number which is unique for each transaction. How do I add a column to the text? It seems to stop working if I add for exampel a index column or the TrID to the "Raggruppate righe" row.

 

I also wrote my code as it is to fetch that excel table I sent you, however my excel table lacked the "TrID".

 

let
    Source = Sql.Database("SSEHILSQL03\", "FT", [Query="SELECT  #(lf)TrID,#(lf)TrDt,#(lf)convert(varchar,TrDt,112) as Date,#(lf)OrdID,#(lf)TrmID,#(lf)ProdID#(lf)FROM Tr (NOLOCK)#(lf)#(lf)WHERE convert(varchar,TrDt,112)=20201126 AND#(lf)(TrmID='2070517' OR TrmID='2070203')#(lf)AND TrTp=6 #(lf)AND Tr.OrdTp=4 #(lf)AND Tr.SysDel=0#(lf)ORDER BY TrmID, TrID;"]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each [Index] - Table.Last [Index])
in
    #"Added Custom"

 

 

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.