cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
govi Regular Visitor
Regular Visitor

Calculate time between 2 rows with 2 criteria

Hi,

I have to calculate the difference in time (in minutes; timestampDateTime) between a Started and a Stopped action.

Remarks:

  • The timestampDateTime is sorted ascending.
  • Action can be started on one day and stopped on another
  • The userID has to be the same
  • When there is only a Started nothing is calculated

Below is an example of the table:

table.png

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
mussaenda New Contributor
New Contributor

Re: Calculate time between 2 rows with 2 criteria

hi @govi ,

 

if you are open to pivoting your data, try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc07CoAwFETRrUhqAzMvRuPrXINlsBC0VsT94w+Dou3lMBOjaQCa3AhtZQWsM4EWbi/t2i/rOJgu/0PUojzRNM8J0b8QReE/S5QD2XAZBEX9b9xtCHXuc4awpyoZUeK5020=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [userID = _t, timestampDateTime = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"userID", type text}, {"timestampDateTime", type datetime}, {"Action", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Action]), "Action", "timestampDateTime"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Duration Minutes", each if [Stopped] = null then "" else
Duration.TotalMinutes([Stopped]-[Started])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration Minutes", type number}})
in
    #"Changed Type1"

2019_10_02_16_14_48_Untitled_Power_Query_Editor.png

View solution in original post

3 REPLIES 3
mussaenda New Contributor
New Contributor

Re: Calculate time between 2 rows with 2 criteria

hi @govi ,

 

if you are open to pivoting your data, try this:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc07CoAwFETRrUhqAzMvRuPrXINlsBC0VsT94w+Dou3lMBOjaQCa3AhtZQWsM4EWbi/t2i/rOJgu/0PUojzRNM8J0b8QReE/S5QD2XAZBEX9b9xtCHXuc4awpyoZUeK5020=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [userID = _t, timestampDateTime = _t, Action = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"userID", type text}, {"timestampDateTime", type datetime}, {"Action", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Action]), "Action", "timestampDateTime"),
    #"Added Custom" = Table.AddColumn(#"Pivoted Column", "Duration Minutes", each if [Stopped] = null then "" else
Duration.TotalMinutes([Stopped]-[Started])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Duration Minutes", type number}})
in
    #"Changed Type1"

2019_10_02_16_14_48_Untitled_Power_Query_Editor.png

View solution in original post

govi Regular Visitor
Regular Visitor

Re: Calculate time between 2 rows with 2 criteria

Hi @mussaenda ,

 

Thank you for helping me out.

You code has to be put in the Advanced Editor but where?

 

This is already in it:

Let
    Source = Web.Contents(
	"XXXXXXXXX",
	[
        RelativePath="query",
		Headers=[
			#"Method"="POST",
			#"Accept-Encoding"="gzip",
			#"x-api-key"="XXXXXXXXXX"
		],
		Content=Text.ToBinary("{""query"": ""{questionnaires(id:136){demoSubscriptions{user{id},events{timestampDateTime,action,actionData}}}}""}")
	]
    ),
    #"JSON" = Json.Document(Source),
    data1 = JSON[data],
    questionnaires1 = data1[questionnaires],
    questionnaires2 = questionnaires1{0},
    demoSubscriptions1 = questionnaires2[demoSubscriptions],
    #"Converted to Table" = Table.FromList(demoSubscriptions1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"user", "events"}, {"Column1.user", "Column1.events"}),
    #"Expanded Column1.user" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.user", {"id"}, {"id"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Column1.user",{{"id", "userID"}}),
    #"Expanded Column1.events" = Table.ExpandListColumn(#"Renamed Columns", "Column1.events"),
    #"Expanded Column1.events1" = Table.ExpandRecordColumn(#"Expanded Column1.events", "Column1.events", {"timestampDateTime", "action", "actionData"}, {"timestampDateTime", "action", "actionData"}),
    #"Expanded actionData" = Table.ExpandRecordColumn(#"Expanded Column1.events1", "actionData", {"key", "value", "stage", "timing", "score", "symptom_type", "nostril"}, {"key", "value", "stage", "timing", "score", "symptom_type", "nostril"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded actionData",{"key", "value"}),
    #"Inserted Date" = Table.AddColumn(#"Changed Type", "Date", each DateTime.Date([timestamp]), type date),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"score", "symptom_type", "nostril"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([action] = "Stopped" or [action] = "Started")),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"timestamp", Order.Ascending}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Sorted Rows",{"stage", "timing"})
in
    #"Removed Columns2"

So where do I put it in this query?

 

Thank you!!

 

govi

mussaenda New Contributor
New Contributor

Re: Calculate time between 2 rows with 2 criteria

Hi @govi ,

 

You are right, you will place it in advance editor.

But place it in a blank Query.

Then from there, follow the steps to your table.

You are the one can decide where you will apply the steps.

 

The query povided is based on your sample data and will serve as your guide only.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors