Hi,
Please see attached workbook. I am trying to transform a table via Power Query, but can't seem to wrap my head around it. The input data can be seen in the tab "Input" and the desired output from Power Query can be seen in the tab "Output".
The tab "Example" contains the "Input" data, in addition to explanations of which data I am trying to find and formula solutions for this. These formulas work fine with small data samples, but the workbook becomes too slow with larger data sets.
Formula for identifying rows which contain the desired data:
Formula for retrieving desired data from the relevant column in the identified rows:
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 (see tab "Example"):
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 F, using only the data contained in columns A to E.
Can anybody please help me with this in Power Query?
Best regards,
Marbleking
Bookmarks