Calculate difference between two times using Pivot Table Calculated Field

[mouzzampk2014]

Hi, I tried different steps two subtract two times (cell b-cell a) using calucated fieldd and output the total in cell c but my output is totally wrong. Could someone look into this for me. Much appreciated. Shall I upload sample file? Thank you

 

[mouzzampk2014]

Hehe I can see its working exactly what I was looking for but not sure how can I use this with my exisitng data?

 

[sandy666]

Ab ovo...

Source = Excel.CurrentWorkbook(){[Name="[COLOR="#0000FF"]Table1[/COLOR]"]}[Content], - this is the name of your source table
yours will be probably like this:
Source = Excel.CurrentWorkbook(){[Name="Raw_Data_Pivot"]}[Content],

so try From Table then adapt the code except first line

or post the M-code here

 

[mouzzampk2014]

You are the best you done it. Only one thing is missing from the table is SERNO. It was there before we run this query but after I run above query SERNO gone

 

[sandy666]

you only showed a piece of what you want with changed names. It only works with your example so you have to do everything from the beginning as in the example step by step and not just copy and paste

edit:
your Pivot Table doesn't contain SERNO
so add SERNO to Group in PowerQuery and you'll see it in QueryTable and in PivotTable (I hope )
I removed unnecessary columns from QueryTable to get only what I see in your result PT
but it doesn't make sense if SERNO is not used in PT

 

[mouzzampk2014]

This is what I have

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"SERNO", type text}, {"INITS", type text}, {"STATE", Int64.Type}, {"MAX_CPHIST_TIME", type time}, {"MIN_CPHIST_TIME", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DATE", "INITS"}, {{"Max", each List.Max([MAX_CPHIST_TIME]), type time}, {"Min", each List.Min([MIN_CPHIST_TIME]), type time}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Grouped Rows", "Subtraction", each [Max] - [Min], type duration)
in
#"Inserted Time Subtraction"

How can I add SERNO into the table so I can count number of SERNO against INITS between START AND FINISH? I can't believe you just resolved my whole issue in just few minutes
[/FONT]

 

[mouzzampk2014]

Basically thats the output I am looking for

 

[sandy666]

like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]DATE[/td][td=bgcolor:#DDEBF7](All)[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]INITS[/td][td=bgcolor:#DDEBF7]Sum of Max[/td][td=bgcolor:#DDEBF7]Sum of Min[/td][td=bgcolor:#DDEBF7]Sum of Subtraction[/td][td=bgcolor:#DDEBF7]Count of SERNO[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ABH[/td][td]

11:02:00​

[/td][td]

10:46:00​

[/td][td]

0.00:16:00​

[/td][td]

73​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]BR[/td][td]

00:03:00​

[/td][td]

23:32:00​

[/td][td]

0.00:31:00​

[/td][td]

36​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]STF[/td][td]

16:15:00​

[/td][td]

13:11:00​

[/td][td]

0.03:04:00​

[/td][td]

21​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Grand Total[/td][td=bgcolor:#DDEBF7]

03:20:00​

[/td][td=bgcolor:#DDEBF7]

23:29:00​

[/td][td=bgcolor:#DDEBF7]

0.03:51:00​

[/td][td=bgcolor:#DDEBF7]

130​

[/td][/tr]
[/table]

re-download file (post #20 )

 

[sandy666]

How can I add SERNO into the table so I can count number of SERNO against INITS between START AND FINISH? I can't believe you just resolved my whole issue in just few minutes
[/FONT]

check steps in PQ, and more specifically Group

 

[mouzzampk2014]

I done this

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]let
Source = Excel.CurrentWorkbook(){[Name="Raw_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"SERNO", type text}, {"INITS", type text}, {"STATE", Int64.Type}, {"MAX_CPHIST_TIME", type time}, {"MIN_CPHIST_TIME", type time}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"DATE", "INITS", "SERNO"}, {{"Max", each List.Max([MAX_CPHIST_TIME]), type time}, {"Min", each List.Min([MIN_CPHIST_TIME]), type time}}),
#"Inserted Time Subtraction" = Table.AddColumn(#"Grouped Rows", "Subtraction", each [Max] - [Min], type duration)
in
#"Inserted Time Subtraction"

but after this

Subtraction results changed to

_{^{<strike>
</strike>}}[/FONT]
And PivotTable show me like that which is wrong result



But your example shows exactly what I want so I am not sure what I am doing wrong

 

[mouzzampk2014]

If you look at ABH on 04/02/2019 Start Time was 10:41 not 14:29