Forum Discussion
For each code, Lookup values and summ all values
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.
Anonymous ,
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
1 Reply
Anonymous ,
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