Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have to calculate the difference in time (in minutes; timestampDateTime) between a Started and a Stopped action.
Remarks:
Below is an example of the table:
Thanks
Solved! Go to Solution.
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"
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |