Power Query - Remove duplicates but retain data that may only appear once

Hi,

I am several years behind the times and I have jumped into the world of power query, so far I've found it to be a relatively straight forward tool to use (except my dodgy attempts to modify the code or source data which breaks it).
Anyways I have a situation I can't seem to find a solution too so I'm handing it over to the professionals. That means you lot!

So the data I am loading into PQ has duplicate entries and I want to remove them which is easy enough. The problem is that some fields/columns of data contain a value only once within the duplicates - I need to populate the null values before I remove duplicates so I don't lose any information during the ETL process.

Attached is a crude example with mock data to show the stages I want to create. I know that I can sort the ID ascending then the date descending an finally fill the date down but with the example attached ID 'C' doesn't have a value and would be populated with the date from ID 'B'. 'C' somehow needs to remain blank/null.

Hopefully the above makes sense, please let me know if you require clarification on anything.

Thanks
Harribone

Please try

Sort ID ascending, Sort Date descending, Filled up

Please Login or Register  to view this content.

Hi Harribone,

No PQ needed in this PT answer. See the attached..

PT no PQ Example Harribone.xlsx

Sorry for delay. Work and illness changed my priorities.

Thanks both for feedback
Bo_Ry - The method you suggested works for the example however when there are more entries to handle this won't work (the blank ones will be anywhere within the data and fill up or down will just use the data for another item).
MarvinP - Was hoping to avoid doing within excel just to reduce the steps etc. and keep things nice and tidy however a pivot table never came to mind! Should've seen this from day one!

Mucho thanks
Harribone