Forum Discussion

SafeAnalyst's avatar
SafeAnalyst
Frequent Visitor
5 years ago
Solved

How to Lookup value from latest date for specific value

Hi.   I have the following dataset where i would like to locate the 'Item Legder Entry Type' for each Item No based on the last posting date. I have the column 'Last Posting Date' and i would like ...
Need Help
  • Smauro's avatar
    Smauro
    5 years ago

    Hi SafeAnalyst ,

     

    Using [Last Posting Date] :

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNbDUNTIwMlBQ0lFSQAIBpUXJGYnFqUBhVzcDY0tzIANZdaxOtJIBkG8O5sM1+6WmJ5ZklqUqOKZklRaX5KbmleAzwchY18AM1QQ4cM7PKy7NLSjJzM/Da4QJjA+URHW1iYkRSANCAVi9EWnqgWwDQyDX0BIoB/deIpr3TMzNgAwktbGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Item Ledger Entry Type" = _t, #"Item No" = _t, #"Last Posting Date" = _t]),

    #"Keep Last Dates" = Table.SelectRows(Source, each [Posting Date] = [Last Posting Date])
in
    #"Keep Last Dates"


    Using just [Posting Date] :

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNbDUNTIwMlBQ0lFSQAIBpUXJGYnFqUBhVzcDY0tzIANZdaxOtJIBkG8O5sM1+6WmJ5ZklqUqOKZklRaX5KbmleAzwchY18AM1QQ4cM7PKy7NLSjJzM/Da4QJjA+URHW1iYkRSANCAVi9EWnqgWwDQyDX0BIoB/deIpr3TMzNgAwktbGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Item Ledger Entry Type" = _t, #"Item No" = _t, #"Last Posting Date" = _t]),

    LastDates = Table.Group(Source[[Item No], [Posting Date]], {"Item No"}, {{"Max Date", each List.Max([Posting Date]), type date}}),
    #"Keep Last Dates" = Table.NestedJoin ( Source, {"Item No", "Posting Date"}, LastDates, {"Item No", "Max Date"}, "Remove", JoinKind.Inner),
    #"Remove Extra Column" = Table.RemoveColumns( #"Keep Last Dates", {"Remove"})
in
    #"Remove Extra Column"
SafeAnalyst's avatar
SafeAnalyst
Frequent Visitor
5 years ago

Actually AlB i have some issues with your code. It me be because i haven't expressed myself clearly enough.

 

I have added som more data to my example because your code only filters to the item numbers with the posting date of 29-09-2020.

 

Posting DateItem Ledger Entry TypeItem NoLast Posting Date
28-09-2020             PurchaseEF039728-09-2020 
08-07-2020      Negative AdjustmentEF039728-09-2020 
23-06-2020               ConsumptionEF039728-09-2020 
24-09-2020PurchaseEF044224-09-2020
22-09-2020PurchaseEF044224-09-2020
09-01-2019Negative adjustmentEF047609-01-2019
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNbDUNTIwMlBQ0lFSQAIBpUXJGYnFqUBhVzcDY0tzpVidaCUDoHpzsHq4cr/U9MSSzLJUBceUrNLiktzUvBJUPUbGugZmqHrgwDk/r7g0t6AkMz8PTZMJzGFAcVS3mJgYQZQYEVQClDcwBCoxtASKwx2aiOZQE3MzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Item Ledger Entry Type" = _t, #"Item No" = _t]),
    #"Duplicated Column" = Table.DuplicateColumn(Source, "Posting Date", "Last Posting Date"),
    #"Grouped Rows" = Table.Group(#"Duplicated Column", {"Item No"}, {{"max Posting Date", each Table.Max(_, {"Posting Date"})}}),
    #"Expanded max Posting Date" = Table.ExpandRecordColumn(#"Grouped Rows", "max Posting Date", {"Posting Date", "Item Ledger Entry Type", "Last Posting Date"}, {"Posting Date", "Item Ledger Entry Type", "Last Posting Date"})
in
    #"Expanded max Posting Date"

     

     

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrLQNbDUNTIwMlBQ0lFSQAIBpUXJGYnFqUBhVzcDY0tzpVidaCUDoHpzsHq4cr/U9MSSzLJUBceUrNLiktzUvBJUPUbGugZmqHrgwDk/r7g0t6AkMz8PTZMJzGFAcVS3mJgYQZQYEVQClDcwBCoxtASKwx2aiOZQE3MzpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Posting Date" = _t, #"Item Ledger Entry Type" = _t, #"Item No" = _t]),
    maxList=Table.ToRecords(Table.Group(Source, "Item No",{"Posting Date", each List.Max([Posting Date])})),
    tsr=Table.SelectRows(Source, each List.Contains(maxList, [[Item No],[Posting Date]]))
in
    tsr