Power Query: Copy earliest date/timestamp for connected rows to each of those rows

Lexer · Aug 18, 2022

Hi,

Please see attached mini sheet. I am trying to transform a table via Power Query. The input data can be seen in the columns A to E and the desired output from Power Query are the same columns, but also including column F.

The table rows in the mini sheet are sorted (partially manually) for to better illustrate how rows are related. However, in real data sets, the rows are not sorted. I have formulas in column K and L which create the output I want to see in column F. These formulas work fine with small data samples, but the workbook becomes too slow with larger data sets.

Some explanation of the principle behind the data generated in the table:

The table contains database entries. I have only included those columns which are relevant for solving the problem at hand. When a new row is entered into the database, it will get the value "false" in column C. The value "false" simply means that the entry hasn’t (yet) been revised; that all columns (many of which haven’t been included in this sample) retain its original entry. This will also be evident in that the date/timestamp in column E, named Changed, is equal to the date/timestamp in column D, named Created.

However, sometimes a database entry will be revised. What happens then is that the originally entered row will be kept as it is, except that the value in column C will be changed to "true", signifying that the row has been changed. Also, the date/timestamp in column E will be changed to the date/timestamp that the change happened.

Changing a row like this will automatically create a new row in the database, which contains the altered information. This new row will have the value "false" in column C, like the originally entered row once had, and the date/timestamp in column D will equal the date/timestamp in column E. Notably, the date/timestamp in column D named Created for this row will be equal to the date/timestamp in column E named Changed for the row that was altered, since the new row was created in the same instant that the original row was altered. This date/timestamp relationship makes it possible to identify that these two rows in the table are indeed related and should be grouped together.

If there are several revisions for a specific database entry, it is the latest row for this entry, containing the value "false" in column C and the most recent date/timestamp in columns D and E, that will be revised, leaving behind yet another row with the value "true" in column C, as explained earlier. Thus, there may be several rows with the value "true" in column C that are chronologically linked to the final entry, for which the value in column C will always be "false".

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 as explained above, find the earliest related date/timestamp for these, and copy that date/timestamp to each row for the group in a new column.

Here is the formula that, for each row within a specific group, identifies the row containing the earliest date/timestamp for that group:

Excel Formula:

=IF(SUMPRODUCT(($A$2:$A$42=$A2)*($B$2:$B$42=$B2)*($C$2:$C$42="True")*(E$2:E$42=$D2)*(ROW($C$2:$C$42)<>ROW()))<>0,INDEX($K$1:$K$42,AGGREGATE(14,6,ROW($C$2:$C$42)/(($A$2:$A$42=$A2)*($B$2:$B$42=$B2)*(C$2:$C$42="True")*($E$2:$E$42=$D2)),1)),ROW()-ROW($K$1))

Here is the formula for retrieving the relevant date/timestamp based on the rows identified with the previous formula:

Excel Formula:

=INDEX($D$2:$D$42,$K2,)

Power Query timestamp lookup.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

1

Batch

Code

Row changed (true = line has one or more successors and may have one or more predeccessors. false = line has no successors, but may have one ore more predeccessors).

Created (timestamp will be equal to immediate predecessor's (if any) "Changed" timestamp)

Changed (timestamp will be equal to immediate successor's (if any) "Created" timestamp)

Desired result from Power Query operations

Table rows

Table rows which contain correct results in "Created" column (reflected in column "Desired result from Power Query operations"

Hierarchy for colored example rows (1 = oldest/ original)

FORMULA for traced back rows

FORMULA for traced back dates

Test

2

123456

AA

false

12.11.2018 09:38:20

1

Cell Formulas
Range	Formula
K2:K42	K2	=IF(SUMPRODUCT(($A$2:$A$42=$A2)($B$2:$B$42=$B2)($C$2:$C$42="True")(E$2:E$42=$D2)(ROW($C$2:$C$42)<>ROW()))<>0,INDEX($K$1:$K$42,AGGREGATE(14,6,ROW($C$2:$C$42)/(($A$2:$A$42=$A2)($B$2:$B$42=$B2)(C$2:$C$42="True")*($E$2:$E$42=$D2)),1)),ROW()-ROW($K$1))
L2:L42	L2	=INDEX($D$2:$D$42,$K2,)
M2:M42	M2	=L2=F2

Power Query: Copy earliest date/timestamp for connected rows to each of those rows

Lexer

New Member

Excel Facts

GraH

Well-known Member

Lexer

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock