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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Arnaud_37
New Member

Calculate date between 2 rows

Hi all,

 

I want to calculate the duration of an action.

I have a row which indicates the DateTime when the action starts. The column "Message" ends with "démarrée".

I have another row which indicates the DateTime when the action ends. The column "Message" ends with "terminée"

I want to add a column and calculate the Date difference between "terminée" and "démarrée"

 

Arnaud_37_0-1656408596449.png

thanks for your help

 

Arnaud_37

1 ACCEPTED SOLUTION

@Arnaud_37 

 

Okay, so if you revert to your second screenshot (your initial response to me), you could select one of the pivoted columns (start or end) and then select 'Fill' from the Transform tab within the ribbon (Up or Down will depend on if you've used start or end). Then you can just deselect the NULLs from the other column and I think this should get you there.

View solution in original post

9 REPLIES 9
BITomS
Resolver IV
Resolver IV

Hi @Arnaud_37,

 

You can use the Pivot Column functionality. If you select the Message column and go to 'Transform' in the ribbon > Pivot Column, you can Set the value as DateHeure (Don't Aggregate):

 

OEMTomS_0-1656431228694.png

 

Then you can go to 'Add Column' within the ribbon to add a new custom column which will be a simple End minus Start time formula to get the duration. Hope this helps.

Hi @BITomS ,

Thank you for your answer

The problem is that I always find myself having to calculate a duration according to 2 dates that are on two lines

Arnaud_37_0-1656504269447.png

Regards

@Arnaud_37 providing all other column values, other than Message and DateHeure, are the same for the start and end rows, this should not happen (i.e. if all other columns have consistent values that relate to the same 'action', pivoting the data will consolidate everything into one row).

 

Your latest screenshot suggests there is (at least) one column where the row values do not match for the same action, so you need to ensure your table only contains relevant columns with consistent values per action - looking at your screenshot from your original post, I am wondering if you have removed the 'Heure' column before attempting to pivot the data? As this is different across both start and end rows, it is probably what is causing your data to not pivot correctly. Try removing this column first and see if this makes a difference.

@BITomS

 

I simplified my columns and I get:

Arnaud_37_0-1656506882604.png

 

but when i pivot the "message" column with the "DateHeure" column then i have an error message: "There are too many elements in the enumeration to complete the operation"

Arnaud_37_1-1656507024020.png

 

@Arnaud_37 

 

Ha, I think you have removed too many columns now - if you only have Message and DateHeure, there is no other column for Power Query to reference to identify that a start and end time relates to the same action. Power Query will be assuming all those start and end times relate to the same single action, so cannot logically apply all those times to 2 columns within the same row, creating the error.

 

Without having access to your model, I would assume there must be an ID column or similar in order to distinguish one action from another? If this is not available, it is this lack of an ID column that will be causing you an issue for calculating between 2 times - otherwise how else will Power Query know which start time relates to which end time?

I understand,

But my data come from log file.

I have one line where it is written "Process started" (with date, time and other things).

the line after write "Process ended".

the line after write "Process started"

....

@Arnaud_37 

 

Okay, so if you revert to your second screenshot (your initial response to me), you could select one of the pivoted columns (start or end) and then select 'Fill' from the Transform tab within the ribbon (Up or Down will depend on if you've used start or end). Then you can just deselect the NULLs from the other column and I think this should get you there.

thanks a lot

EBoklund
Frequent Visitor

I think that what you're looking for is DATEDIFF.

https://www.absentdata.com/calculate-time-between-two-dates/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors