Forum Discussion

govi's avatar
govi
Icon for Helper II rankHelper II
7 years ago
Solved

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:

Thanks

Need Help
  • mussaenda's avatar
    mussaenda
    7 years ago

    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"

3 Replies

  • mussaenda's avatar
    mussaenda
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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"

    • govi's avatar
      govi
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        mussaenda
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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.