When the type is actual department is empty. But I want the department field to be populated for all the actual type rows based on the Project ID like below. See for the project Id with type Target and get its Department and fill the same department for all the actual type records. How can I do this in Tableau?

Since your data has Null values in Department you would have to create a Group in Tableau under Proj ID field and group ID 1 & 2 under Sales, 3 under IT, and 20 under HR - then use that Group to display the results.

I will be having thousands of Unique project Ids. Also this list is not going to be a constant one. It will keep changing and new unique project ids will get added on a daily basis. So I would need a dynamic way in tableau to do this.

I duplicated the data source and filtered one copy at the data source level based on type as actual and the other copy based on type as target. This is allowing me to do visualizations the way I wanted it. Thank you for your help. But I just hoped a simple and straight forward way using a calculated field and one single data source.