Remove earliest or first instance of field in Power Query

[Tob_P]

Hi everyone,

Is there a way in Power Query whereby I can remove the earliest (or first) row of a table for each unique entry?

So for the first entry in this table based on Document No CQ0130449, I want to remove the one that has Date Sent of 17/05/2023 and repeat the process for each of the subsequent Doc Nos in the table?

Thanks

[Tob_P]

Hi @Vijay_A_Verma I went for your first suggestion and that works perfectly for me, so thank you.

@m_dekorteAlso thank you, your response came in whilst I was trying the first suggestion out and I can see how that would also work.

Thanks again to the community for showing there's always more than one way to do something so I've learned two things today!

[m_dekorte]

Hi @Tob_P,

Your data appears to be sorted, try something like this:
1. Select the Document No column

2. Choose Group By, select the operation "All Rows" and specify a new column name, let's say "Temp"

3. In the formula bar you'll find this: {{"Temp", each _    (with some more code behind it...)

4. Replace: each _  With: each Table.RemoveFirstN(_, 1)

5. Use the Expand column option, to bring all data back.

When your data is not sorted you can nest a Table.Sort as well

I hope this is helpful

[Vijay_A_Verma · Accepted solution]

If you want only first row to be removed in each group, insert this step where Custom1 needs to be replaced with your previous step

= Table.Combine(Table.Group(Custom1, {"Document No_"}, {{"All", each Table.RemoveRows(_, 0)}})[All])

If you want to remove all rows which has same earliest date, then

able.Combine(Table.Group(Custom1, {"Document No_"}, {{"All", each Table.RemoveMatchingRows(_, {[Date Sent = List.Min(_[Date Sent])]}, "Date Sent")}})[All])