Calculate time difference to previous matching row using If statements

[lukasjar]

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),

Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Cycle time]

I should get a new column, with the difference between TrDt on RowX and RowY, as long as TrmID, Date and OrdID is the same.

In below case thedifference is (presented in a new column):

Row1 is empty/0 since there is nothing to compare to.

Row2 00:00:25 (hh:mm:ss) between row1 and row2.

Row3 00:00:27 (hh:mm:ss) between row2 and row3.

and so on.

Table loaded.

My load is as below, sorting by TrmID, and secondly TrID (unique number).

SELECT  
TrID,
TrDt,
convert(varchar,TrDt,112) as Date,
OrdID,
TrmID,
ProdID
FROM Tr (NOLOCK)

WHERE convert(varchar,TrDt,112)=20201126 AND

ORDER BY TrmID, TrID;

I have this code in Qlikview but dont understand how to apply it in Power BI/PowerQuery.

LOAD
TrID,
TrDt,
OrdID,
TrmID,
UsrID,
ProdID,
ProdNm,
Batch;

SQL SELECT * FROM "FT".dbo.Tr where TrTp=6 AND TrDt>=DateAdd(m, -2, getdate()) AND Tr.OrdTp = 4 AND Tr.SysDel = 0 order by TrmID, TrID;

Ord:
LOAD
	OrdID,
	OrdSt;
//*;
SQL SELECT * FROM "FT".dbo.Ord;

mapOrderStatus:
Mapping LOAD Distinct
	OrdID,
	OrdSt
Resident Ord;

FACT:
LOAD 
	RecNo() as %KeyRow,
	TrID,
	if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID=Previous(OrdID),Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Cycle time],
	if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID=Previous(OrdID),1,0) as _cntCycleTime,
	if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID<>Previous(OrdID),Interval(timestamp(TrDt)-timestamp(Previous(TrDt)),'mm:ss'),0) as [Switch time],
	if(TrmID=Previous(TrmID) and Date(floor(TrDt))= previous(Date(floor(TrDt))) and OrdID<>Previous(OrdID),1,0) as _cntSwitchTime,
	if(Date(floor(TrDt))<> previous(Date(floor(TrDt))),1,0) as _flgNewDate,
	ApplyMap('mapOrderStatusName',ApplyMap('mapOrderStatus',OrdID,''),'<n/a>') as [Order status],
	TrmID as [Terminal ID],
	OrdID as [Order no],
	if(OrdID<>Previous(OrdID),1,0) as _flgNewOrder,
	UsrID as [User ID],
	ProdID as Item,
	ProdNm as [Item name],
	Batch,
	Timestamp(TrDt) as TimeStamp,
	Date(floor(TrDt)) as %Date,
	time(TrDt) as Time,
	1 as _cntProduct
Resident Tr order by TrmID,TrID;	

drop tables Tr, Ord;

 Thanks for any advice!

[Anonymous]

i can't read you code. but if you give a source table and the result table i can give it a try.

of course the tables must be easily copied

[lukasjar]

@Anonymous  excel files with raw data and result table can be found on below link

raw data and result table files 

[Anonymous]

let
    Origine = Excel.Workbook(File.Contents("C:\Users\xxx\lexit data.xlsx"), null, true),
    Sheet2_Sheet = Origine{[Item="Sheet2",Kind="Sheet"]}[Data],

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


    #"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(_)[CycleTime]}}),
    #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Raggruppate righe", "CycleTime"),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}})
in
    #"Modificato tipo2"

[Anonymous · Accepted solution]

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]

I have also uploaded my pbix to the dropbox.

pbix file 

[lukasjar]

Thank you but unfortunately I am not getting it to work with my sql database.

As I wrote, when I add more columns to the group by I just get null back from the calcCycle. Code below.

Cycle is null

let
    Source = Sql.Database("SSEHILSQL03\", "FT_Thule", [Query="SELECT  #(lf)    #(lf)TrID,#(lf)TrDt,#(lf)TrDt as DateTime,#(lf)TrmID,#(lf)OrdID,    #(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;"]),
    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,
    #"Change type" = Table.TransformColumnTypes(#"Source",{
        {"TrID", Int64.Type},
        {"TrDt", type datetime},
        {"DateTime", type datetime},
        {"TrmID", Int64.Type},
        {"OrdID", Int64.Type}, 
        {"ProdID", Int64.Type}}),
    #"Split datetime" = Table.SplitColumn(Table.TransformColumnTypes(#"Change type", {{"TrDt", type text}}, "sv-SV"), "TrDt", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"TrDt.1", "TrDt.2"}),
    #"Change type of date and time" = Table.TransformColumnTypes(#"Split datetime",{{"TrDt.1", type date}, {"TrDt.2", type time}}),
    #"Rename date and time" = Table.RenameColumns(#"Change type of date and time",{{"TrDt.1", "Date"}, {"TrDt.2", "Time"}}),
    #"Group rows by calcCycle" = Table.Group(#"Rename date and time", {"Date", "DateTime", "TrID", "TrmID", "OrdID", "ProdID"}, {{"CycleTime", each calcCycle(_)}}),
    #"Expanded CycleTime" = Table.ExpandListColumn(#"Group rows by calcCycle", "CycleTime"),
    #"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Expanded CycleTime", "CycleTime"),
    #"Modificato tipo2" = Table.TransformColumnTypes(#"Tabella CycleTime espansa",{{"CycleTime", type duration}})
in
    #"Modificato tipo2"

Do you have any clue why this happens?

[Anonymous]

I have several as to why this happens :-).

I can point you to something wrong, but without being able to test the code I cannot give you a complete fix.
I do not have much time ... so you should use goole (as I would)

La prima cosa che non va è l'istruzione duplicata ...

#"Expanded CycleTime" = Table.ExpandListColumn(#"Group rows by calcCycle", "CycleTime"),
#"Tabella CycleTime espansa" = Table.ExpandListColumn(#"Expanded CycleTime", "CycleTime"),

Controlla se la colonna DateTime esiste (forse la colonna si chiama Time)

#"Group rows by calcCycle" = Table.Group(#"Rename date and time", {"Date", "DateTime", "TrID", "TrmID", "OrdID", "ProdID"}, {{"CycleTime", each calcCycle(_)}}),

Controlla se la colonna DateTime esiste. Qua non si dovrebbe trovare nessuna colonna dateTime.

#"Change type" = Table.TransformColumnTypes(#"Source",{
{"TrID", Int64.Type},
{"TrDt", type datetime},
{"DateTime", type datetime},
{"TrmID", Int64.Type},
{"OrdID", Int64.Type},
{"ProdID", Int64.Type}}),

Per quanto riguarda l'aspetto dimensinale dovresti fornire in numero di righe e la dimensione tipica dei sottogruppi.

Quanti prodotti, quante transazioni per ogni prodotto e quante operazioni per ogni transazione, ecc

[lukasjar]

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]

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]

I have uploaded them to dropbox, "lexit result 2" and "lexit data 2".

files for: lexit result 2, lexit data 2 

[Anonymous]

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.

I added a column wich give you the size of each OrderID group.

this is the head of the results

and this is the tail of the first group.

[lukasjar]

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]

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]

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.