For each code, Lookup values and summ all values

[HBL_Nick]

Hi All,

I'm not sure how to explain my question, so I'll try using an example, I got a dataset like this, a project with multiple cost codes:

Description (text)	Relevant Codes (text)
Project one	P01, P03, P05
Project two	P02, P04, P06

The codes with the cost on them: 

Projectcode (text)	Cost (number
P01	100
P02	20
P03	40
P04	10
P05	5
P06	30

Now I want to make a table with a calculation of how much each project has in cost in total:

project description (text)	Cost (number)
Project one	145
Project two	60

Can any one help? Thank you in advance.

[Arul · Accepted solution]

@HBL_Nick ,

follow the below steps,

1. In power query editor split the relevant code text column by delimiter,

instead of columns you have select rows (refer the image attached)

2. Then close and apply and create the relationship between Table 1 and Table 2  like the below,

3. Pull the required columns to the visuals,

Thanks,

Arul