Latest Value per Article

[joshua1990]

Hi all!

I have a big table that contains dimensional an transaction data:

Article	Dim 1	Dim 2	Dim 3	Value	Date
A
A

Now I would like to get the latest [Value] based on the Date column.

I know, DAX would be here the best way, but I need it in PQ.

How would you do that? Filter for each Article the last available row based on the Date column.

Thanks in advance

[serpiva64]

Hi,

you can try by grouping

then after expanding you have to filter for one article

= Table.SelectRows(#"Expanded AllRows", each ([MinDate] = #date(2022, 1, 24)))

and replace in your formula bar

= Table.SelectRows(#"Expanded AllRows", each ([MinDate] = [Order Date]))

Pay attention if you have duplicates in your table (in that case you have to add grouping f.e. you have sold the same article to 2 customer in the same day) !!!

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this:
Example data:

1. Duplicate the query for use in step 3
2. Group the query by date and article:

#"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Date", "Article"}),
#"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Article"}, {{"LatestDate", each List.Max([Date]), type nullable date}})
in
#"Grouped Rows"

3. Merge the grouped query and filter nulls away after expanding:
#"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Date"}, #"Table (27)", {"LatestDate"}, "Table (27)", JoinKind.LeftOuter),
#"Expanded Table (27)" = Table.ExpandTableColumn(#"Merged Queries", "Table (27)", {"LatestDate"}, {"LatestDate"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Table (27)", each ([LatestDate] <> null))
in
#"Filtered Rows"


4. End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/