Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
govi
Helper II
Helper II

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
mussaenda
Super User
Super User

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
Super User
Super User

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

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.