Scenario
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.
Can you go over this thread (post #8) - think it might help you with what youāve described.
Otherwise just create some dummy data, doesnāt matter what it is as long as the structure- and data types are the same as your actual source. Provide some additional context so members can better assist.
That wouldnāt work for me as not all records will exist in the most recent file as each daily file only contains data for the last 90 days. Iāll have records that will drop off the daily file but still need to have Max Record Date applied against them as we will have them in a big history table.
I merge the Code, Reference & Date of Event columns to create my Unique Identifier for Max Date column.
Unique Identifier for Max Date column will be used to create the Output based on the File Date column.
Apologies if Iām doing a terrible job of explaining this or havenāt included what you need.
@Melissa Thank you so much, it had been annoying me, I knew it was possible I just didnāt work out the steps to do it. I had tried using Group By but that was after creating a duplicate query and merging them back together and matching the Reference and then a conditional column.
Your way is far slicker, thank you for taking the time out to help and for turning it around so quickly.
@Melissa Iām new to PowerBI and am thankful for your post on how to solve this Max Date based on another column.
I stumbled at your step 3 and kept getting an error that said couldnāt recognise āExpandDataā. I have worked out another way to add the output column - by using āAdd Conditional Columnā from the Add Columns tab.
