Creating a new Dataset column using a map from a second Dataset

Here's a function that finds the last location according to datasetB:

g[assoc_] := datasetB[Select[#Id <= assoc["Id"]&] /* Last, "Location"]

Using this function, you can augment datasetA to include location information with:

datasetA[All, Association[#, "Location" -> g[#]]&]

enter image description here


Here's another method I hinted at in my earlier comment, but didn't have time for to post yet. It consists of 2 steps: first I use JoinAcross to get the known locations across and then I use FoldList to update the missing locations:

Dataset @ FoldList[
  If[ MissingQ[#2["Location"]],
      Append[#2, "Location" -> #Location],
      #2
  ]&,
  SortBy[#Id &] @ JoinAcross[Normal[datasetA], Normal[datasetB], Key["Id"], "Left"]
]