Forum Discussion
Calculate date between 2 rows
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.
I simplified my columns and I get:
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"
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"
....
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