cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bigtree123
Helper I
Helper I

How to create this calculated column (Power Query)

I have a table 'Allocation' which looks like:

 

Year Week Person Project HoursAllocated
202127AX2
202127AY3
202127BZ5
202127CZ6
202128AX

3

202128AY

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-2021202127AX1
06-07-2021202127AX1
08-07-2021202127AY2
09-07-2021202127AY1
10-07-2021202127BZ3

 

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?

2 ACCEPTED SOLUTIONS
Marik
Solution Supplier
Solution Supplier

@bigtree123 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])

View solution in original post

Hi, @bigtree123 

 

In Power Query:

 

1. Merge Queries

2. Hold down the shift key to select multiple matching columns, and click OK

3. Aggregate

vangzhengmsft_0-1635128173513.png

Result:

vangzhengmsft_2-1635128572870.png

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.

View solution in original post

3 REPLIES 3
Marik
Solution Supplier
Solution Supplier

@bigtree123 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])

This works well! Thanks

 

I am wondering if its possible to do the same thing in Power Query? As I would like to filter the rows of this table using the new column created.

Hi, @bigtree123 

 

In Power Query:

 

1. Merge Queries

2. Hold down the shift key to select multiple matching columns, and click OK

3. Aggregate

vangzhengmsft_0-1635128173513.png

Result:

vangzhengmsft_2-1635128572870.png

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.

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Users online (526)