Forum Discussion
How to create this calculated column (Power Query)
I have a table 'Allocation' which looks like:
| Year | Week | Person | Project | HoursAllocated |
| 2021 | 27 | A | X | 2 |
| 2021 | 27 | A | Y | 3 |
| 2021 | 27 | B | Z | 5 |
| 2021 | 27 | C | Z | 6 |
| 2021 | 28 | A | X | 3 |
| 2021 | 28 | A | Y | 8 |
This table represents on a week-person-project level, how many hours are allocated.
I have another table 'TimeRegistered', which looks like:
| Date | Year | Week | Person | Project | HoursRegistered |
| 05-07-2021 | 2021 | 27 | A | X | 1 |
| 06-07-2021 | 2021 | 27 | A | X | 1 |
| 08-07-2021 | 2021 | 27 | A | Y | 2 |
| 09-07-2021 | 2021 | 27 | A | Y | 1 |
| 10-07-2021 | 2021 | 27 | B | Z | 3 |
This table represents on a daily-person-project level, how many hours were registered.
My goal is to add a column 'HoursRegistered' to the 'Allocation' table, which summarizes at the week-person-project level how many hours were registered.
How can I achieve this?
Anonymous Try this:
Calc_column=var Current_Year='Allocation'[Year]
var Current_Week='Allocation'[Week]
var Current_Person='Allocation'[Person]
var Current_Project='Allocation'[Project]
var tempTable=FILTER('TimeRegistered','TimeRegistered'[Year]=Current_Year&&'TimeRegistered'[Week]=Current_Week&&'TimeRegistered'[Person]=Current_Person&&'TimeRegistered'[Project]=Current_Project)
return SUMX(tempTable,[HoursRegistered])
Hi, Anonymous
In Power Query:
1. Merge Queries
2. Hold down the shift key to select multiple matching columns, and click OK
3. Aggregate
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Anonymous Try this:
Calc_column=var Current_Year='Allocation'[Year]
var Current_Week='Allocation'[Week]
var Current_Person='Allocation'[Person]
var Current_Project='Allocation'[Project]
var tempTable=FILTER('TimeRegistered','TimeRegistered'[Year]=Current_Year&&'TimeRegistered'[Week]=Current_Week&&'TimeRegistered'[Person]=Current_Person&&'TimeRegistered'[Project]=Current_Project)
return SUMX(tempTable,[HoursRegistered])
- v-angzheng-msft
Community Support
Hi, Anonymous
In Power Query:
1. Merge Queries
2. Hold down the shift key to select multiple matching columns, and click OK
3. Aggregate
Result:
Please refer to the attachment below for details. Hope this helps.
Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.