Forum Discussion
Helper ISplit Timesheet Hours Rows by Percentages of Cost Center Allocations
I am looking for assistance to help with the following data from Project Online:
- Split Timesheet hours into separate rows based on Project-level Cost Center(s) percentage allocations (number of project-level cost centers can be from 1-10)
- Override above Project-level Cost Center allocations if a Task-level Cost Center is entered for that task in the project schedule.
The visuals below have fields from one merged table. I have been able to split timesheet hours in a matrix visual by cost center, but am having difficulty to split into separate rows displaying a task allocated hours for each cost center. The bottom visual is as close as I can get so far...
I would like the output to be something similar to the following table. The image below has additional fields from the merged table. Please take note that the line with a Task Cost Center is allocated 100% to the cost center shown which overrides the Project Cost Center(s) for that project. This is the second part of my query for help!
I can share the .pbix file for reference. Any help is appreciated..!
3 Replies
v-cherch-msftMicrosoft Employee
Hi V3GAS
You may try to use Unpivot columns in Query Editor. Then merge queries as requested. If it is not your case, please share a simplified data sample.
Regards,
Cherie
- V3GAS
Cherie,
I may not be doing it correctly, but everytime I try to unpivot columns in Query Editor, I receive errors.
Here is a link to the same data, but a simplified version in Excel: Month End Details
For each timesheet line, it will have 1 or more Cost Centers and the % of Allocation to distribute the Actual Work hours.
I am looking to create a separate line for each task/cost center with distributed hours to something like this:
- v-cherch-msft
Hi V3GAS
Here is the reference for you. You may try to do some data cleaning first then use unpivot columns.
http://profunda-analytics.com/2017/10/09/lesson-16-pivot-unpivot-part-1/
Regards,
Cherie
