You need to specify a third parameter to applymap like:
ApplyMap('SiteSourceMap', SiteId, 'AlternativeReturnValueIfNoMappingValueCouldBeFound')
This meant your SiteId seems not identically in both tables - maybe SiteId needs a cleaning like trim() or converting with floor() or formating with num() to be matchable.
You can also start with just linking the two tables by SiteId:
SiteId as SiteIdSource,
FROM [lib://Development Final Transformed Data/Site.qvd]
ID as OrderId,
// ApplyMap('SiteSourceMap', SiteId) as SourceData,
SiteId as SiteIdOrder,
FROM [lib://Development Raw Managed Data/Order_Order.qvd]
Then load and create a table box in your front end, using OrderId, SiteIdOrder, SiteId, SiteIdSource as fields in the table. Order by SiteId.
Check where the SiteId* fields in both tables match (located in one row) and where they don't match.
Are you able to see why they don't match? Maybe there are leading zeros missing in one or the other field?