Power Query grouping of related items to find earliest datetime

Hi,

I have a table as can be seen in the attached workbook. I am trying to use Power Query to group those items from column A and column B which have an unbroken relationship through date/timestamps, find the earliest related date/timestamp, and copy that date/timestamp to each row for the group in a new column.

The relationship between grouped items from column A and column B which belong together is as such:

1. The row with newest date/timestamp has the value "false" in column C, along with the newest date/timestamp in column D.

2. The immediate preceding related row has the value "true" in column C, and the same date/timestamp as the succeeding (item 1.) related row, in its column E. This row has an earlier date/timestamp in its column D.

3. The predecessor to the above described row (in 2.) also has the value "true" in column C, and the same date/timestamp in its column E that item 2. has in its column D. Item 3 then has an earlier date/timestamp in its column D.

4. Etc. until there are no more "linked" rows with value "true" in column C. Also, most of the rows don't have any other related rows, but will be "standalone" rows with value "false" in column C (and on occasion the value "true").

I am trying to produce a column with the results seen in column I.

NB: The information in columns F, G and H are for clarifying the relationships only, and shall not be used to achieve the result. These columns will not be present, normally.

Does anybody see how to solve this with Power Query?

Best regards,
Marbleking

As this has been sitting a few days with no response, I'll offer the following formula based proposal:

Formula:

Please Login or Register  to view this content.

Note that the formula doesn't return the expected results for rows 20, 23 and 27, however given the criteria above I feel that the formula output may be correct.
Let us know if you have any questions.

Hi JeteMC,

Thanks for the formula! I requested a formula solution a while ago in the commercial forum and got a solution from samba_ravi that solved the conundrum, please see attached workbook containing two variants of this solution.

Now I hope that there's an efficient way to perform this trick in Power Query.

Regards,
Marbleking