+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

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

    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:

    Please Login or Register  to view this content.
    Formula for retrieving desired data from the relevant column in the identified rows:

    Please Login or Register  to view this content.
    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
    Attached Files Attached Files

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

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

    I didn't understand what you meant with the False/True issues. I have created a PQ query in the attached that resembles the output you have provided. See if that helps.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

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

    Hi Alan, and thanks for your reply!

    You have found the earliest date/time for the combination of the columns Group and Code. But not all rows with the same Group and Code belong together. That's where the False/True and linked dates come into the picture.

    If you look at the tab "Example", and e.g. the rows I have marked in yellow and named III_1, III_2, III_3, III_4 and III_5 for illustration: They are scattered throughout the table, but belong together because of this relationship:

    Row III_5 with Code = False and Created = Changed is the last and final of this group of rows, containing the latest date/timestamp. Row III_4 has the exact date/timestamp in column Changed as row III_5 has in its Created column. That's why they are "linked", in addition to also sharing the same Group and Code. The fact that row III_4 has Code = True means that it has a successor with a later date/timestamp, namely row III_5. Now, row III_3 is also linked to this group of rows, because it has the exact same date/timestamp in column Changed as row III_4 has in its column Created, in addition to also sharing the same Group and Code as III_4. And so forth backwards until row III_1, which has the exact same date/timestamp in its column Changed as row III_2 has in its column Created. Searching the rest of the table will not yield any other rows that belong to this group of rows, because they're not linked the way that is described above. The result I am looking to populate in a new column for this group of rows (III_1 to 5) is the earliest date/timestamp found for these rows, which is found in row III_1's column Created.

    Best regards,
    Marbleking

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

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

    Your explanation is too convoluted for me to understand. Can you explain simply which fields need to be grouped. I clearly don't understand what you mean by linked dates. Don't need to understand your business rationale, but only functionally what you want to happen. Last chance or I will leave this for others.

  5. #5
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

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

    Hi, thanks for looking into this.

    I have updated the workbook with a new tab called "Example2", which I hope helps to better illustrate what I need. I have sorted the rows (partially manually), to better show how various rows should be grouped together. (Normally, the rows are scattered around like shown in the other tabs).

    Most of the rows are stand-alone, single rows. (No row with value "false" will ever be connected to another row which also has the value "false").

    The date/timestamps that are needed for the new column are shown in red.

    I hope this helps.

    Best regards,
    Marbleking
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-12-2021, 03:44 AM
  2. Power Query grouping of related items to find earliest datetime
    By Marbleking in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2021, 03:44 AM
  3. MS Power Query timestamp
    By AlanC95 in forum Excel General
    Replies: 0
    Last Post: 03-01-2021, 12:48 PM
  4. [SOLVED] Power Query - add empty rows & rows of text below bottom of group
    By josephteh in forum Excel General
    Replies: 1
    Last Post: 11-16-2020, 02:59 PM
  5. Modify csv data source that is connected to a XLSM file via power query
    By TheRevenant in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-15-2020, 12:27 PM
  6. [SOLVED] Select Rows between blank rows using Power Query
    By beaglesBuddy in forum Excel General
    Replies: 5
    Last Post: 08-17-2018, 08:30 PM
  7. Replies: 9
    Last Post: 12-09-2010, 12:22 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1