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

Anonymous

Not applicable

5 years ago

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

Not applicable

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

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

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.

Calculate time difference to previous matching row using If statements.pbix35 KB

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.

lukasjar

Resolver I

5 years ago

I have also uploaded my pbix to the dropbox.

pbix file

lukasjar

Resolver I

5 years ago

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
Not applicable
5 years ago
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

Forum Discussion

Calculate time difference to previous matching row using If statements

Related content

Dynamic Calculation Different Period

Different Calculations

DAX Calculate IF OR Statement

If Statement Calculated Column Returning Incorrectly

1. How to add two different calculated columns of two different table