output in seconds.

[PaMa]

Hello everyone,
I'm not sure if I'm right here.
I've been working with Power BI for a good six months. Since last week I'm stuck on the following scenario in Power Query:
A table consists of several columns including the columns "Time" and "PNR"
The "Time" column has the following format: DD.MM.YYYY hh:mm:ss and the "PNR" column has the format Text.
The "PNR" column contains the same values several times. These values must be compared with the "Time" column and the difference in time to these same values should appear as the result. The result is to be output in seconds.
Can someone help me with this?

Thanks in advance

[PaMa]

It's all a bit cumbersome. So, I sorted by PNR. Now I would like to have the difference between the time and the associated PNR. Can this be created in Power Query via a DAX function?

[Vijay_A_Verma]

Use this where Source needs to be replaced

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ListPNR = List.Buffer(Source[PNR]),
    ListTime = List.Buffer(Source[Time]),
    CountTbl = Table.RowCount(Source),
    GenResultList = List.Generate(()=>[x=0,i=0], each [i]<CountTbl, each [i=[i]+1, x=if ListPNR{i+1}=ListPNR{i} then List.Max({Duration.From(0), ListTime{i}-ListTime{i+1}}) else 0 ], each try Duration.From([x]) otherwise Duration.From(0)),
    Result = Table.FromColumns(Table.ToColumns(Source) & {GenResultList}, Table.ColumnNames(Source) & {"Difference"})
in
    Result

[PaMa]

Time	PNR
20.05.2023 20:03:25	D625961
20.05.2023 15:04:55	D627034
20.05.2023 14:50:51	213036
20.05.2023 14:43:56	213036
20.05.2023 14:20:25	213036
20.05.2023 13:23:43	D626093
20.05.2023 12:24:56	D573572
20.05.2023 12:21:22	D599761
20.05.2023 12:21:19	D197163
20.05.2023 12:19:45	D564575
20.05.2023 12:03:59	213036
20.05.2023 11:56:46	213036
20.05.2023 11:56:42	D610731
20.05.2023 11:45:47	D561089
20.05.2023 11:41:06	D638730
20.05.2023 11:36:10	D0078374
20.05.2023 11:28:51	D658661
20.05.2023 06:54:26	D561238
20.05.2023 06:35:35	D197163
20.05.2023 06:33:56	D599761
20.05.2023 05:38:29	D567482
20.05.2023 05:37:30	D625929
20.05.2023 05:35:03	D626034
20.05.2023 05:34:53	D574419
20.05.2023 05:30:49	D565712
20.05.2023 05:28:30	D599710
20.05.2023 05:22:55	D610731
20.05.2023 05:08:41	D146049
20.05.2023 04:53:20	D568920
20.05.2023 04:51:41	D626139
20.05.2023 01:18:34	D626393
19.05.2023 23:29:49	D153135
19.05.2023 23:04:05	D625961
19.05.2023 21:41:37	D594069
19.05.2023 21:38:57	D211959
19.05.2023 21:35:49	D0155647
19.05.2023 21:31:41	D197789
19.05.2023 21:28:59	D625961
19.05.2023 21:27:10	D0211948
19.05.2023 21:17:55	D0219576
19.05.2023 21:16:23	D574420
19.05.2023 21:14:37	D0207420
19.05.2023 21:11:47	D0195838
19.05.2023 21:10:53	D150295
19.05.2023 21:09:28	D0186947
19.05.2023 21:07:53	D145905
19.05.2023 21:06:42	D569652
19.05.2023 21:06:17	D170381
19.05.2023 19:26:11	D569196
19.05.2023 19:22:41	D626183
19.05.2023 18:32:40	D231522
19.05.2023 13:42:49	D0221203
19.05.2023 13:28:15	D0210293
19.05.2023 13:28:09	D569196
19.05.2023 13:27:43	D638730
19.05.2023 13:26:13	D566158
19.05.2023 13:23:40	D626093
19.05.2023 13:22:22	D0220141
19.05.2023 13:20:36	D667734
19.05.2023 13:20:31	D667734
19.05.2023 13:16:16	D599714
19.05.2023 13:13:07	D0075787
19.05.2023 13:11:59	D626034
19.05.2023 13:03:07	D231522
19.05.2023 12:59:20	D0003336
19.05.2023 12:51:54	LK1483
19.05.2023 12:37:48	LK3265
19.05.2023 12:25:24	D633011
19.05.2023 12:07:19	D181146
19.05.2023 12:03:46	D625998
19.05.2023 11:59:32	LK2703
19.05.2023 11:58:39	D565909
19.05.2023 11:48:02	D0223793
19.05.2023 11:12:42	D172245
19.05.2023 10:48:48	D556858
19.05.2023 10:42:50	D649162
19.05.2023 10:26:48	D0210495
19.05.2023 09:54:20	LK1732
19.05.2023 09:48:35	D0050667
19.05.2023 09:44:48	D639099
19.05.2023 09:36:35	D626885

[Vijay_A_Verma]

This is how the data is looking like. Can you tell me the expected answer for any row and the logic followed to arrive at the answer?

[PaMa]

Unfortunately, the seconds were not copied in the Time column.

[PaMa]

Great, Thanks! Here is a small copy of the file

Time	Nr_ZK	PNR
20.05.2023 20:03:25	01501.01.01	D625961
20.05.2023 15:04:55	01501.01.01	D627034
20.05.2023 14:50:51	01501.01.02	213036
20.05.2023 14:43:56	01501.01.01	213036
20.05.2023 14:20:25	01501.01.02	213036
20.05.2023 13:23:43	01501.01.01	D626093
20.05.2023 12:24:56	01501.01.01	D573572
20.05.2023 12:21:22	01501.01.01	D599761
20.05.2023 12:21:19	01501.01.01	D197163
20.05.2023 12:19:45	01501.01.01	D564575
20.05.2023 12:03:59	01501.01.02	213036
20.05.2023 11:56:46	01501.01.01	213036
20.05.2023 11:56:42	01501.01.01	D610731
20.05.2023 11:45:47	01501.01.01	D561089
20.05.2023 11:41:06	01501.01.01	D638730
20.05.2023 11:36:10	01501.01.01	D0078374
20.05.2023 11:28:51	01501.01.01	D658661
20.05.2023 06:54:26	01501.01.01	D561238
20.05.2023 06:35:35	01501.01.01	D197163
20.05.2023 06:33:56	01501.01.01	D599761
20.05.2023 05:38:29	01501.01.01	D567482
20.05.2023 05:37:30	01501.01.01	D625929
20.05.2023 05:35:03	01501.01.01	D626034
20.05.2023 05:34:53	01501.01.01	D574419
20.05.2023 05:30:49	01501.01.01	D565712
20.05.2023 05:28:30	01501.01.01	D599710
20.05.2023 05:22:55	01501.01.01	D610731
20.05.2023 05:08:41	01501.01.01	D146049
20.05.2023 04:53:20	01501.01.01	D568920
20.05.2023 04:51:41	01501.01.01	D626139
20.05.2023 01:18:34	01501.01.01	D626393
19.05.2023 23:29:49	01501.01.01	D153135
19.05.2023 23:04:05	01501.01.01	D625961
19.05.2023 21:41:37	01501.01.01	D594069
19.05.2023 21:38:57	01501.01.01	D211959
19.05.2023 21:35:49	01501.01.01	D0155647
19.05.2023 21:31:41	01501.01.01	D197789
19.05.2023 21:28:59	01501.01.01	D625961
19.05.2023 21:27:10	01501.01.01	D0211948
19.05.2023 21:17:55	01501.01.01	D0219576
19.05.2023 21:16:23	01501.01.01	D574420
19.05.2023 21:14:37	01501.01.01	D0207420
19.05.2023 21:11:47	01501.01.01	D0195838
19.05.2023 21:10:53	01501.01.01	D150295
19.05.2023 21:09:28	01501.01.01	D0186947
19.05.2023 21:07:53	01501.01.01	D145905
19.05.2023 21:06:42	01501.01.01	D569652
19.05.2023 21:06:17	01501.01.01	D170381
19.05.2023 19:26:11	01501.01.01	D569196
19.05.2023 19:22:41	01501.01.01	D626183
19.05.2023 18:32:40	01501.01.01	D231522
19.05.2023 13:42:49	01501.01.01	D0221203
19.05.2023 13:28:15	01501.01.01	D0210293
19.05.2023 13:28:09	01501.01.01	D569196
19.05.2023 13:27:43	01501.01.01	D638730
19.05.2023 13:26:13	01501.01.01	D566158
19.05.2023 13:23:40	01501.01.01	D626093
19.05.2023 13:22:22	01501.01.01	D0220141
19.05.2023 13:20:36	01501.01.01	D667734
19.05.2023 13:20:31	01501.01.01	D667734
19.05.2023 13:16:16	01501.01.01	D599714
19.05.2023 13:13:07	01501.01.01	D0075787
19.05.2023 13:11:59	01501.01.01	D626034
19.05.2023 13:03:07	01501.01.01	D231522
19.05.2023 12:59:20	01501.01.01	D0003336
19.05.2023 12:51:54	01501.01.01	LK1483
19.05.2023 12:37:48	01501.01.01	LK3265
19.05.2023 12:25:24	01501.01.01	D633011
19.05.2023 12:07:19	01501.01.01	D181146
19.05.2023 12:03:46	01501.01.01	D625998
19.05.2023 11:59:32	01501.01.01	LK2703
19.05.2023 11:58:39	01501.01.01	D565909
19.05.2023 11:48:02	01501.01.01	D0223793
19.05.2023 11:12:42	01501.01.01	D172245
19.05.2023 10:48:48	01501.01.01	D556858
19.05.2023 10:42:50	01501.01.01	D649162
19.05.2023 10:26:48	01501.01.01	D0210495
19.05.2023 09:54:20	01501.01.01	LK1732
19.05.2023 09:48:35	01501.01.01	D0050667
19.05.2023 09:44:48	01501.01.01	D639099
19.05.2023 09:36:35	01501.01.01	D626885
19.05.2023 09:35:45	01501.01.01	D231551
19.05.2023 09:35:23	01501.01.01	D626841
19.05.2023 09:11:13	01501.01.01	D181693
19.05.2023 09:06:17	01501.01.01	D197575
19.05.2023 08:42:05	01501.01.01	D562801
19.05.2023 08:41:05	01501.01.01	D0201545
19.05.2023 08:40:54	01501.01.01	D0201545
19.05.2023 08:40:53	01501.01.01	212537
19.05.2023 08:40:50	01501.01.01	D0201561
19.05.2023 08:40:48	01501.01.01	D0201561
19.05.2023 08:37:24	01501.01.01	D589612
19.05.2023 08:34:36	01501.01.01	D562759
19.05.2023 08:33:31	01501.01.01	D0058782
19.05.2023 08:26:18	01501.01.01	D561133
19.05.2023 08:20:07	01501.01.01	LK3961
19.05.2023 08:18:59	01501.01.01	LK0980
19.05.2023 08:17:31	01501.01.01	D564697
19.05.2023 08:11:26	01501.01.01	LK10540
19.05.2023 08:10:26	01501.01.01	LK2703

[Vijay_A_Verma]

Please post some sample data for both columns.