comparing value in the cells below and calculating difference if it is true

[kkarol]

Hi Community,

So i have a table: 

	a	b	c	d	e	f	g	h
1	datetime	name	status	data	day	month	timestamp
2	10/10/24 13:10	aaabbb	a	hdgoew	10/10	10	13:04:15
3	10/10/24 13:08	aaabbb	a	hdfakwp	10/10	10	13:08:14
4	11/10/24 14:10	aaabbb	o	ksdhfk	11/10	10	14:10:58
5	11/10/24 13:10	aaaccc	a	kfkwye	11/10	10	13:10:47

and what i need here is to calculate difference between timestamps if name and day are the same. 

In excel i do it this way: in cell h2 if b2=b3 and day is the same (e2=e3) and status is different from o (c2<>"o") i deduct g3-g2, else null. I need to translate it to powerquery because the formula in excel takes hour or so to calculate.

any ideas? 

appreciate any help.

K

[Anonymous · Accepted solution]

Hi @kkarol ,

Please try:

let
    Source = Table.FromRows(
        {
            {"10/10/24 13:10", "aaabbb", "a", "hdgoew", "10/10", 10, "13:04:15"},
            {"10/10/24 13:08", "aaabbb", "a", "hdfakwp", "10/10", 10, "13:08:14"},
            {"11/10/24 14:10", "aaabbb", "o", "ksdhfk", "11/10", 10, "14:10:58"},
            {"11/10/24 13:10", "aaaccc", "a", "kfkwye", "11/10", 10, "13:10:47"}
        },
        {"datetime", "name", "status", "data", "day", "month", "timestamp"}
    ),
    ChangeType = Table.TransformColumns(Source, {
        {"datetime", each DateTime.FromText(_), type datetime},
        {"timestamp", each Time.FromText(_), type time}
    }),
    SortedTable = Table.Sort(ChangeType, {{"name", Order.Ascending}, {"day", Order.Ascending}, {"timestamp", Order.Ascending}}),
    IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),
    PreviousRowTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),
    MergedTable = Table.NestedJoin(
        IndexedTable,
        "Index",
        PreviousRowTable,
        "Index",
        "PreviousRow",
        JoinKind.LeftOuter
    ),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"name", "day", "status", "timestamp"},{"PreviousRow.name","PreviousRow.day","PreviousRow.status","PreviousRow.timestamp"}),
    AddTimeDifference = Table.AddColumn(ExpandedTable, "TimeDifference", each 
        if [name] = [PreviousRow.name] 
           and [day] = [PreviousRow.day] 
           and [status] <> "o" then 
            Duration.TotalSeconds(Time.From([timestamp]) - Time.From([PreviousRow.timestamp])) 
        else 
            null,
        type number
    ),
    FinalTable = Table.RemoveColumns(AddTimeDifference, {"Index", "PreviousRow.name", "PreviousRow.day", "PreviousRow.status", "PreviousRow.timestamp"})
in
    FinalTable

Before

After

Best Regards,

Bof

[Omid_Motamedise]

Use the group by command to grouped similar rows then apply the differenc over the grouped rows

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQByIjEwVDYytDAyUdpcTExKSkJBADiDNS0vNTy4EMsDIwDSKMrQxMrAxNlWJ1UA0wsMA0IC0xu7wAmwkWVoYmEBMMYSaYoDkhH4izi1My0rJBegyRDQAptTK1QDMA4Yfk5GSoE7LTsssrUzEMACm1MjFXio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [datetime = _t, name = _t, status = _t, data = _t, day = _t, month = _t, timestamp = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"timestamp", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"name", "day"}, {{"Count", each _}})
in
#"Grouped Rows"