Forum Discussion
Distribute data over defined categories
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?
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
5 Replies
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
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.
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".
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
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!
