I think you already know the answer from what you've said, but maybe just don't know the function! You should use ApplyMap()
ApplyMap('Map_PrefixInvest',Year&SubField(Code,'_',1)&'_',0)) AS Flag_Project
Hope this helps,
I'm not sure you can concatenate mapping tables. Give it a go, otherwise you could create a standard data table using CONCATENATE and then build the mapping table from this, then drop the standard table.
Using Index(), or Left(), or similar usually means you have to know how many characters the prefix will be. Using SubField(), the delimiter (underscore in this case) can be anywhere in the string and it will still work. Besides, ApplyMap() and SubField() are two of my favourite functions...