Forum Discussion

Tob_P's avatar
Tob_P
Icon for Helper V rankHelper V
2 years ago
Solved

Remove earliest or first instance of field in Power Query

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

Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    2 years ago

    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])

     

3 Replies

  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    Icon for Most Valuable Professional rankMost Valuable Professional
    2 years ago

    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])

     

  • m_dekorte's avatar
    m_dekorte
    Icon for Resident Rockstar rankResident Rockstar
    2 years ago

    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

  • Tob_P's avatar
    Tob_P
    Icon for Helper V rankHelper V
    2 years ago

    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!