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
vazfelipe
Frequent Visitor

Calculating time difference in a vertical column

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?

 

Capturar.PNG

1 ACCEPTED 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.

 

groupby.PNG

 

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])

Hours.PNG

 

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

View solution in original post

7 REPLIES 7
drad2211
Frequent Visitor

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
Impactful Individual
Impactful Individual

Try the solution in the below link:

 

https://community.powerbi.com/t5/Desktop/Sum-hours/td-p/55781

 


@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.

 

groupby.PNG

 

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])

Hours.PNG

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.