Filter on last row for each article

[joshua1990]

Hi experts!

I have a weekly archive of our sales date, structured like this:

Year-Week	Article	Team	Country	Department	Group
2025-01	A	A	AAAA	AA	AAA
2025-02	A		AAAA	AA

In reality this archive has already like 5Mio rows and 50 columns.

Here I would like to remove all columns that contains transactional information like Sales etc. and to keep all master data. But here I would like to keep the last row for each article.

In the end something like this

Article	Team	Country	Department	Group	Last Entry
A		AAAA	AA		2025-02

How would you do that in Power Query?

[Akash_Varuna · Accepted solution]

Hi @joshua1990 Could you try this please 

• Remove Unnecessary Columns:

  • Select master data columns and use Remove Other Columns.

• Sort by Article and Year-Week:

  • Sort Article (ascending) and Year-Week (descending).

• Group by Article:

  • Go to Home → Group By:
    • Group by Article and choose All Rows.

• Keep the Latest Row:

  • Add a custom column with:
    Table.FirstN([All Data], 1)
  • Expand the table to show required fields.

• Add "Last Entry" Column:

  • Create a column for Year-Week and rename it Last Entry.
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[PwerQueryKees · Accepted solution]

From

Using

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"Article", type text}, {"Team", type text}, {"Country", type text}, {"Department", type text}, {"Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article"}, {{"Rows", each Table.Last(_)}}),
    #"Expanded Rows" = Table.ExpandRecordColumn(#"Grouped Rows", "Rows", {"Year-Week", "Team", "Country", "Department", "Group"}, {"Year-Week", "Team", "Country", "Department", "Group"})
in
    #"Expanded Rows"

To

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[v-pbandela-msft]

Hi @joshua1990,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @joshua1990,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @joshua1990,

Thank you for reaching out in Microsoft Community Forum.

Thank you @Akash_Varuna , @SundarRaj , @PwerQueryKees   for the helpful response.

As recommended by AKash_Varuna, SundarRaj, PwerQueryKees.,  we trust that the provided solution is accurate and has resolved your issue.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and select "Yes" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[SundarRaj · Accepted solution]

Hi @joshua1990 , here's a solution you could look at. In case of any clarifications or if I haven't got your query correctly, don't hesitate to correct me or ping back. I'll attach the image of the code below. Thanks!

[PwerQueryKees · Accepted solution]

From

Using

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year-Week", type date}, {"Article", type text}, {"Team", type text}, {"Country", type text}, {"Department", type text}, {"Group", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Article"}, {{"Rows", each Table.Last(_)}}),
    #"Expanded Rows" = Table.ExpandRecordColumn(#"Grouped Rows", "Rows", {"Year-Week", "Team", "Country", "Department", "Group"}, {"Year-Week", "Team", "Country", "Department", "Group"})
in
    #"Expanded Rows"

To

Did I answer your question? Then please (also) mark my post as a solution and make it easier to find for others having a similar problem.
Remember: You can mark multiple answers as a solution...
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[Akash_Varuna · Accepted solution]

Hi @joshua1990 Could you try this please 

• Remove Unnecessary Columns:

  • Select master data columns and use Remove Other Columns.

• Sort by Article and Year-Week:

  • Sort Article (ascending) and Year-Week (descending).

• Group by Article:

  • Go to Home → Group By:
    • Group by Article and choose All Rows.

• Keep the Latest Row:

  • Add a custom column with:
    Table.FirstN([All Data], 1)
  • Expand the table to show required fields.

• Add "Last Entry" Column:

  • Create a column for Year-Week and rename it Last Entry.
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

[joshua1990]

Thanks, what mean All Data hear? 

• Table.FirstN([All Data], 1)

Seems like it is supposed to be a column. 

[Akash_Varuna]

It si the column created after Group By Function when you do group by it creates a column