Forum Discussion

joshua1990's avatar
joshua1990
Post Prodigy
3 years ago
Solved

Latest Value per Article

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

Need Help
  • ValtteriN's avatar
    ValtteriN
    3 years ago

    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/




2 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    3 years ago

    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/




  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    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!