Create a New Column That References Previous Record in Another Table

[arhomberg]

Hello Everyone!

I am looking to find a way to create a new column in a table that looks for a record in another table with the associated id, and pulls a column from it.

Above is a picture of the current data layout. I would like to create the 'New Create Date' in my Table2 that references the previous 'systemmodstamp' in Table1 for the matching 'id', which would be "7/8/2021 19:08". You'll notice that it has to be the most recent 'systemmodstamp' for that specific record. The pattern is that the next most recent entry in the system is suppsoed to have a 'createddate' of the previous records 'systemmodstamp'.  If there is no version of the Table2 'id' in Table1, it should do nothing.

Any advice on how to approach this would be greatly appreciated.

Thanks!

[Anonymous]

Remember that in Power Query you can use JOINS like in SQL (the option: Merge Queries) and filtering. On top of that you've got the full power of the M language at your fingertips...

[Anonymous]

Are you doing this in Power Query? Because this is the place where you should do it.

[arhomberg]

@Anonymous I was not sure where the best place for this would be done. My initial thought was a dax expression, but I could move this to the power query page if this is where things should be aimed! Thanks!