Power Query - get data from another column based on date

Hi, powerplatp0 

You can try the following methods.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLNTMzNBNJm+ob6RgZGJkCmc35uQU5qSapSrE60khGSGiOEGs+8ZKgqBbAyY6BYSGJuQSKQNjTUN8QwDKIMJOJflJOYl5IPZJnrGxqA1JmCNDu5gFWYoqgw1TeGKUBxlRmaKkOsqsxBbk/NScovLcpLBbIt9Y1MQQqNUDygFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Location = _t, Date = _t, Status = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Location", type text}, {"Date", type date}, {"Status", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Location"}, {{"Data", each _}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows",{"Data", each Table.FirstN(_,1)}),
    #"Expanded Data" = Table.ExpandTableColumn(Custom1, "Data", {"ID", "Status"}, {"Data.ID", "Data.Status"}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"ID"}, #"Expanded Data", {"Data.ID"}, "Expanded Data", JoinKind.LeftOuter),
    #"Expanded Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Expanded Data", {"Data.Status"}, {"Data.Status"})
in
    #"Expanded Expanded Data"

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.