Distribute data over defined categories

[tokyobaer]

I would appreciate support with the following problem. We are concerned with a project / ressource planning system: There are projects BM-001 to BM-006 that are planned on an hours basis with ressources RESS01 to RESS03 (Table 1).

PRJ-ID	Ressource	Hours
BM-001	RESS01	12
BM-001	RESS02	23
BM-001	RESS03	34
BM-002	RESS02	56
BM-002	RESS03	67
BM-003	RESS01	89

Now the ressources are organized in groups: RESS02 is always in Grp4, RESS03 is always in Grp5 - but RESS01 is distributed over groups Grp1 to Grp3. This is done differently for each project - for some projects all of them are from one group (e. g. BM-003), for others there is a defined share (BM-001 to BM-002) - all organized in Table 2.

PRJ-ID	Grp1	Grp2	Grp3
BM-001	10%	20%	70%
BM-002	20%	40%	40%
BM-003	0%	100%	0%

So now we want to combine and transform both tables, desired result:

PRJ-ID	Ressource	Group	Hours
BM-001	RESS01	Grp1	1,2
BM-001	RESS01	Grp2	2,4
BM-001	RESS01	Grp3	8,4
BM-001	RESS02	Grp4	23
BM-001	RESS03	Grp5	34
BM-002	RESS01	Grp1	11,2
BM-002	RESS01	Grp2	22,4
BM-002	RESS01	Grp3	22,4
BM-002	RESS03	Grp5	67
BM-003	RESS01	Grp2	89

A simple merge and unpivot does not do the job, leading to unwanted duplicates for RESS02 and RESS03. Any hint how this could be achieved in Power Query and / or DAX?

[tokyobaer]

Correction: There was a typo in Table 1...

PRJ-ID	Ressource	Hours
BM-001	RESS01	12
BM-001	RESS02	23
BM-001	RESS03	34
BM-002	RESS01	56
BM-002	RESS03	67
BM-003	RESS01	89

But this should not change anything about the previous discussion regarding the "partial unpivot".

[jdbuchanan71 · Accepted solution]

Hi @tokyobaer 

Sorry I missed that bit about RESS02/RESS03.  We can do it using two queries from table 1.  The first we filter to only RESS01 and apply table 2.  The second we filter out RESS01 then apply the GRP logic.  Finally we combined those table together.

I updated my .pbix file  https://www.dropbox.com/s/nipbf7dgs45t78r/ProjectHours.pbix?dl=0

[tokyobaer]

That's cool - it works exactly as I intended. With a bit of additional studying I learned a lot about Power Query and the M language over the weekend, and I am impressed... Thank you!

[jdbuchanan71]

@tokyobaer 

You have some lines in your output that I am not sure how you arrived at.  Amounts for Grp4 and Grp5 but they are not in table 2.  That being said, I think we can get what you are looking for by doing the unpivot on table 2 then removing the zeros before doing the merge and calc.

I have posted my sample file here: https://www.dropbox.com/s/nipbf7dgs45t78r/ProjectHours.pbix?dl=0

[tokyobaer]

Hey @jdbuchanan71 ,

thank you very much for your quick reply! As mentioned in my original post, RESS02 is always in Grp4 and RESS03 is always in Grp5. So if you want, there is another Table 2B:

Ressource	Group
RESS01	Shared (see Table 2)
RESS02	Grp4
RESS03	Grp5

That is why a direct unpivot / merge did not work out - it should only be performed on the RESS01, while for RESS02/RESS03 there is the other rule (Table 2A). Somehow a "partial unpivot" transformation.