I have an issue with a query.
We get a daily file where it gives us the details of individual records for the last 90 days regardless of whether the data in the records have changed or not. So I could potentially have the same identical record 90 times with the only differentiator being the date of the file (which is the date the file is received on).
We had thought to use a calculated column but we are going to deal with a LOT of data so figured this would be expensive performance wise.
I have created a Unique Key which identifies the individual record (Unique Identifier for Max Date) and I also have a File Date column. I’d love to be able to create a column through M which identifies the Max Date of the Unique Identifier for Max Date and returns “Max Date” for the latest date and “Previous Record” for the other values. I’ve posted, hopefully correctly, a picture below of some of the columns I’m dealing with. I’m struggling with the M code, hopefully I’ve mapped out the issue clearly.