Transforming date intervals on a per month basis for pivot table

[zits1]

I would like to create a pivot table that displays which employee works on which project in which month. For instance, if you look at my sample table, Tom works a total of 2 months, thereof 0.8 in accouting and 1.2 in service. The pivot table is really great for that.

The challenging part for me is that my raw table contains start and end dates, so date intervals. The pivot table, however, works with full months. So my question is how I can break down the date intervals to the specific months.

What I have is the raw data (Table 1) and the result that I would like to see (Pivot Table). However, for creating the pivot table, I had to transform my raw data manually into Table 2. So, how can I get from Table 1 to the Pivot Table automatically? I tried to do the transformation with PowerQuery, but that didn't work. Any thoughts? Thanks in advance!!

[sandy666]

you mean something like this?

pqt.png

project.png

[zits1]

basically yes! how did you do this?

[sandy666]

try

Please Login or Register  to view this content.

then pivot from query

[zits1]

First of all, thank you very much! That already helps, but it's not yet exactly what I need. However, I think that the solution is close.

Please Login or Register  to view this content.

This step creates exactly two rows per entry. What (I guess) I need is one row per month, so I believe we need a loop that iterates for the number of months that are between the start date and the end date. Then, the column "Monthly Share" should contain the portion of days that fall in this month. For instance, there should only be one line for Lisa with a value of 0.5 for monthly share, because there are only 15 days between the start date and the end date.

Secondly, but this should be easy to fix, the new column "Monthly Share" needs to be multiplied with the existing column "Project Share", so that the correct value is shown in the pivot table. So in my mind, "Project Share" stands for the percentage that a person is engaged in a project (e.g., 1.0=full time; 0.4=40% of working time); this parameter is given. "Monthly Share" stands for portion of the month that needs to be count (e.g. 0.5 for half a month); this parameter is calculated.

[sandy666]

You haven't added any formula to Table2 so how do you know what the value calculation is there.
Even in the post there is no mention of this

I know you know but I don't know what you know

Post example file with details and correct calculation (formula or something) how to get proper values not manually inserted

[zits1]

Sorry, that's right. I have now created a sample file that contains all the steps. The ideal for me would be to have a formula that creates the pivot table directly from table 1 (without the steps of table 2, 3 and 4)

[sandy666]

ok, I'll look at this a bit later

[sandy666]

check this one (not optimised)

[zits1]

How did you do the magic? Looks great! How can I reproduce the green table?

[sandy666]

See M-code in Advanced Editor and steps on the right side in Power Query window

imho you will need nore info about PQ
https://docs.microsoft.com/en-us/power-query/

[zits1]

Thank you so much!!