Copy previous row value including IF statement

[fishboneox]

Good morning, i know there are quite a few similar topics but unfortunately I'm not able yet to adopt them to my problem.

Localizing moving objects I'm using an inertial sensor on an Arduino board. Every time I notice movement I have created a flag value 1. Now I'm trying to get the Start and End Date of this movement and combine them to one "Drive". Calculating the start end value isn't the problem, but to create a row to identify them as one movement.

Currently my table is looking like this:

Since the value of this "Drive_ID" doesn't matter I was using the Index value for the first not blank value in the Start_movement column.

Now I try to Copy the value for Drive_ID from the row above as long Start-movement or End_movement isn't blank but somehow I'm not able to.

Any ideas of an easy solution? Is there any different way of how to get this information. The idea is to use a Gantt chart later to display

[MattAllington]

I’m guessing that the Start movement and End Movement Data is auto populated. Hi about this?

1. Add a custom column concatenating start movement and end movement i to 1 column. 

2. Remove any row in the new column that is Nul

3. Delete column created in 1 

this should leave just rows t have a time stamp. 

Then fill the end movement column up. You may need to fill tblank cells with null first, depending on your data 

then you should have all the start movements with the same end movement time.  

Add a difference in time column. The longest (max) value for each end movement must be the correct row. 

Hope it makes sense. I would need sample data to do this properly - this is just a best guess. 

[fishboneox]

The Start and End Date is collected from a different column containing a key of date and time.

To be able to use the data later for the Gantt chart I need a different start and end column linked by an ID (here Drive_ID).

I'm not sure if I understand your answer. Here you can find some sample data:

Timestamp	Movement Flag
23.03.2018 09:41:25	0
23.03.2018 09:41:26	0
23.03.2018 09:41:27	0
23.03.2018 09:41:28	0
23.03.2018 09:41:29	0
23.03.2018 09:41:30	0
23.03.2018 09:41:31	1
23.03.2018 09:41:32	1
23.03.2018 09:41:33	1
23.03.2018 09:41:34	1
23.03.2018 09:41:35	1
23.03.2018 09:41:36	1
23.03.2018 09:41:37	1
23.03.2018 09:41:38	1
23.03.2018 09:41:39	1
23.03.2018 09:41:40	0
23.03.2018 09:41:41	0
23.03.2018 09:41:42	0
23.03.2018 09:41:43	1
23.03.2018 09:41:44	1

[MattAllington]

I'm happy to have a go at it.  But I'm not clear what your starting position is.  You have provided an image with seemingly a before and after image, and also some sample data.  The "before" image and the sample data are different.  If you can provide some exact sample data, preferably with at least 3 complete events I will take a look (ideally in a table in Excel (Maybe linked via Dropbox or similar)

[fishboneox]

@MattAllington: Thanks for your help!!

Attached you'll find the link with a CSV File. This represents my starting point.

https://we.tl/wFMaTzhOTx

As mentioned above I would like to generate the event start every time a array of several 1 starts and the event "end" every time this array stops. Currently the problem isn't the creation of this event but to connect these events creating one unique ID per array (any kind  of number of unique text) for example:

Using this unique ID I want to be able to group the data.

I hope this time I was able to describe the problem. Since I'm relatively new to PowerBI I would appreciate some step by step explanation