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"
Smauro's avatar
Smauro
Solution Sage
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

Thankt you Smauro!

 

I have a question regarding the Table.NestedJoin part of the code. How do you merge the same table with an earlier version of it self? Is it only possible when writing the code or can you do that through the UI by pressing merge queries?