Need help in merging data

You might still be able to do this with Fill Down if you sort on MST_TO and Date columns first, otherwise see if this does what you need:

My thinking is to group by item, find the MAX Date for each item, then merge back into table. You could possibly create a custom function to do this for you, but here is the M code for the sample data I created to do this. You will need to paste this into three blank queries in Advanced Editor and name the tables as below to see it in action.



RawData

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUVKK1YEwDBW8SnMqdRSMDIwM4IKoDCdkhpEBugYnrOqUYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Item = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Item", type text}, {"Date", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1)
in
#"Added Index"



FillDate

let
Source = RawData,
#"Grouped Rows" = Table.Group(Source, {"Item"}, {{"Date", each List.Max([Date]), type date}})
in
#"Grouped Rows"



Merge1

let
Source = Table.NestedJoin(RawData, {"Item"}, FillDate, {"Item"}, "FillDate", JoinKind.LeftOuter),
#"Expanded FillDate" = Table.ExpandTableColumn(Source, "FillDate", {"Date"}, {"Date.1"})
in
#"Expanded FillDate"