Forum Discussion
calculate difference between time stamps for unique ID
Resident RockstarI usually tell people NOT to pivot their data, but the quickest / easiest way I can think to resolve this is to put your data into a Pivot table. First, I had to mass change your "T" in your time column to a blank so PowerBI can easily identify it as a Date Time column.
With the **Metric** column selected in Query Editors, choose 'Pivot Column' under 'Transform' Tab. Make sure under Advanced NOT to Summarize.
Now you can easily use DateDiff to calucatle time difference as needed... FOrrest
Very much obliged for this.
However, I notice that I get a number of columns with errors or blanks once I have pivoted. Is this because there are some duplications where an individual has register 'ACCP' twice for the same ID?
- fhill
DOH! I didnt' noticed the duplicates in your data. Can you copy/past some sample data (instead of the picture) so we can quickly dump it into Power BI and play with the manipulation... ?
FOrrest
score time score origin metric 2017-06-24T20:10:04 005056B614ED1ED796A191BDD043A0DA ACCP 2017-06-24T21:43:38 005056B614ED1ED796A191BDD043A0DA CMPN 2017-06-24T20:10:08 005056B614ED1ED796A191BDD043A0DA ENRT 2017-06-24T20:10:14 005056B614ED1ED796A191BDD043A0DA ONST 2017-06-24T19:52:31 005056B614ED1ED796A191BDD043A0DA RCVD 2017-06-24T21:04:05 005056B614ED1ED796A191BDD043A0DA WIP 2017-06-27T06:56:46 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T06:59:29 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T14:35:38 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T14:40:51 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T15:47:25 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T15:49:41 005056B614ED1ED796B988341EDC00DA RCVD 2017-06-27T18:02:26 005056B614ED1ED796B988341EDC00DA RCVD 2017-05-15T17:08:17 005056B61C081ED78EB0DC4AEE5500D8 ACCP 2017-05-15T17:09:24 005056B61C081ED78EB0DC4AEE5500D8 ENRT 2017-05-15T17:27:43 005056B61C081ED78EB0DC4AEE5500D8 ONST 2017-05-15T17:05:35 005056B61C081ED78EB0DC4AEE5500D8 RCVD 2017-05-15T18:25:20 005056B61C081ED78EB0DC4AEE5500D8 WIP 2017-05-15T20:23:45 005056B61C081ED78EB2523FFFCBE0D8 ACCP 2017-05-15T20:23:50 005056B61C081ED78EB2523FFFCBE0D8 ENRT 2017-05-16T03:55:53 005056B61C081ED78EB2523FFFCBE0D8 NCMF 2017-05-15T20:24:01 005056B61C081ED78EB2523FFFCBE0D8 ONST 2017-05-15T20:14:47 005056B61C081ED78EB2523FFFCBE0D8 RCVD 2017-05-15T20:24:27 005056B61C081ED78EB2523FFFCBE0D8 WIP 2017-05-16T21:42:33 005056B61C081ED78ECEA29F86DEE0D8 ACCP 2017-05-16T22:45:37 005056B61C081ED78ECEA29F86DEE0D8 CMPN 2017-05-16T21:42:42 005056B61C081ED78ECEA29F86DEE0D8 ENRT 2017-05-16T22:04:09 005056B61C081ED78ECEA29F86DEE0D8 ONST 2017-05-16T21:37:00 005056B61C081ED78ECEA29F86DEE0D8 RCVD 2017-05-16T22:41:28 005056B61C081ED78ECEA29F86DEE0D8 WIP 2017-05-18T02:18:23 005056B61C081ED78EECB9F8A64240D8 ACCP 2017-05-18T03:24:16 005056B61C081ED78EECB9F8A64240D8 CMPN 2017-05-18T02:18:25 005056B61C081ED78EECB9F8A64240D8 ENRT 2017-05-18T02:38:02 005056B61C081ED78EECB9F8A64240D8 ONST 2017-05-18T02:12:24 005056B61C081ED78EECB9F8A64240D8 RCVD 2017-05-18T02:47:31 005056B61C081ED78EECB9F8A64240D8 WIP 2017-05-18T03:59:55 005056B61C081ED78EEDE57F4D9720D8 ACCP 2017-05-18T04:49:52 005056B61C081ED78EEDE57F4D9720D8 CMPN 2017-05-18T04:00:00 005056B61C081ED78EEDE57F4D9720D8 ENRT 2017-05-18T04:02:21 005056B61C081ED78EEDE57F4D9720D8 ONST 2017-05-18T03:48:50 005056B61C081ED78EEDE57F4D9720D8 RCVD 2017-05-18T03:57:48 005056B61C081ED78EEDE57F4D9720D8 RCVD 2017-05-18T04:45:50 005056B61C081ED78EEDE57F4D9720D8 WIP 2017-05-18T08:29:41 005056B61C081ED78EF139996DD220D8 ACCP 2017-05-18T20:57:14 005056B61C081ED78EF139996DD220D8 CMPN 2017-05-18T08:29:44 005056B61C081ED78EF139996DD220D8 ENRT 2017-05-18T08:29:52 005056B61C081ED78EF139996DD220D8 ONST 2017-05-18T07:39:20 005056B61C081ED78EF139996DD220D8 RCVD 2017-05-18T20:46:41 005056B61C081ED78EF139996DD220D8 WIP
