POWER QUERY: 'Flag' row if it contains the latest date in a category

I have data that looks similar to this:

Model# | Part# | Part last ship date

Each part will have a last ship date that varies from the others, and each model might have several of the same part that only differ based on the last ship date. I need to flag the latest part last ship date for each model in Power Query. Any help appreciated!

Example of what I need:

MODEL | Part | Date | FLAG
ABC | 123 | 4/23/18 |
ABC | 123 | 5/18/18 | 1
DEF | 456 | 5/10/18 | 1
DEF | 456 | 4/30/18 |
DEF | 456 | 3/1/17 |

Thanks!

Hi 4am,

I'm not sure you can do this in Power Query. I say that because PQ might have 5 million rows it is filtering and wouldn't know the max until it got through all of them.

How about using a MaxIfS() formula once you pull your data back into Excel? Would that work for you?

Hi sandy,

I was looking at List.Max and List.MaxN from the #shared query but couldn't figure either of them out. I keep thinking that PQ is a line by line process and can pull in millions of rows of data. I don't know how it would keep track of the largest value without needing to pull all lines in first. This thread is on my watch list as I'd like to see and answer too.

OK 4am and Sandy,

See if the attached does what you requested. Instead of adding a column with a 1 or nothing, this example simply saved the Max Date rows for a bunch of random Models and Parts. Here is the PQ (M Language) code:

Please Login or Register  to view this content.

It is much easier to open the attached file and step though the steps. (I didn't need to write a word of the code above )

PQ Group By Advanced Max Date.xlsx

OK,

Here is a better answer using Power Query.

If the Max table is done using a Connection Only and then this is Merged with the original table it is much cleaner. The new column is added using a Conditional Column. See the attached and step through the PQ.

PQ last shipped by Model and Part.xlsx