Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
I have two columns, one with repetead dates and other one with the hour the transaction occured.
Do there's a way to calculate the time duration in certain day?
For example, here below I have many transactions in the day twelve, which occured in a range of time.
How many hours do I have in the day twelve?
Solved! Go to Solution.
Hi @vazfelipe,
To group the Date Column and SUM the hours in that range of date, you can firstly group by the "Order Day" column and get the Min and Max "Order Hour" in the range of the date.
Then you should be able to use the formula(M) below to add a custom column to SUM the hours in that range of date.
=Duration.Hours([MaxHour]-[MinHour])
Following is the M query of all steps for your reference:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRX0lEytjIwtDIwUHD0VYrVQZExsjIxAckEYMgY45QxQZUx0jeGyRgaWJkY4ZAyRJKKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Day" = _t, #"Order Hour" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Day", type date}, {"Order Hour", type time}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Day"}, {{"MinHour", each List.Min([Order Hour]), type time}, {"MaxHour", each List.Max([Order Hour]), type time}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.Hours([MaxHour]-[MinHour])) in #"Added Custom"
Regards
Hi
I have a somewhat similar question. I have a table that has the following columns (ID, Time, Action) and I want to add a new column (I believe by making a DAX equation) that can show the time difference (in mins) for specific ID and the 'In' 'Out' Action. I would like the time difference to be shown only on the 'Out' row. The data file is titled 'Data' and the table looks like:
ID Time Action
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out
2 17:00:00 05/05/2015 Out
. . .
. . .
And I would like the table to look like:
ID Time Action Time Diff
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out 180
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out 120
2 17:00:00 05/05/2015 Out 420
. . .
. . .
Any advice would be helpful.
Thank you
@Omega wrote:Try the solution in the below link:
https://community.powerbi.com/t5/Desktop/Sum-hours/td-p/55781
Does not work because this a DAX Language. I am using Power Query, M Language.
I need to group the Date Column and SUM the hours in that range of date.
Hi @vazfelipe,
To group the Date Column and SUM the hours in that range of date, you can firstly group by the "Order Day" column and get the Min and Max "Order Hour" in the range of the date.
Then you should be able to use the formula(M) below to add a custom column to SUM the hours in that range of date.
=Duration.Hours([MaxHour]-[MinHour])
Following is the M query of all steps for your reference:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRX0lEytjIwtDIwUHD0VYrVQZExsjIxAckEYMgY45QxQZUx0jeGyRgaWJkY4ZAyRJKKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Day" = _t, #"Order Hour" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Day", type date}, {"Order Hour", type time}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Day"}, {{"MinHour", each List.Min([Order Hour]), type time}, {"MaxHour", each List.Max([Order Hour]), type time}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.Hours([MaxHour]-[MinHour])) in #"Added Custom"
Regards
Hi
I have a somewhat similar question. I have a table that has the following columns (ID, Time, Action) and I want to add a new column (I believe by making a DAX equation) that can show the time difference (in mins) for specific ID and the 'In' 'Out' Action. I would like the time difference to be shown only on the 'Out' row. The data file is titled 'Data' and the table looks like:
ID Time Action
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out
2 17:00:00 05/05/2015 Out
. . .
. . .
And I would like the table to look like:
ID Time Action Time Diff
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out 180
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out 120
2 17:00:00 05/05/2015 Out 420
. . .
. . .
Any advice would be helpful.
Thank you
Hi
I have a somewhat similar question. I have a table that has the following columns (ID, Time, Action) and I want to add a new column (I believe by making a DAX equation) that can show the time difference (in mins) for specific ID and the 'In' 'Out' Action. I would like the time difference to be shown only on the 'Out' row. The data file is titled 'Data' and the table looks like:
ID Time Action
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out
2 17:00:00 05/05/2015 Out
. . .
. . .
And I would like the table to look like:
ID Time Action Time Diff
1 08:00:00 05/05/2015 In
1 11:00:00 05/05/2015 Out 180
2 12:00:00 05/05/2015 In
1 13:00:00 05/05/2015 In
1 15:00:00 05/05/2015 Out 120
2 17:00:00 05/05/2015 Out 420
. . .
. . .
Any advice would be helpful.
Thank you
Hi @vazfelipe,
In Power Query, right click on the first column and select Group by. Select the function there as SUM.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |