title,body,link,tags,view_count,is_answered,score,answer_count
Distributing the hours from master table to all the matching values in reference table using excel formula or power queries,"<p>My master table contains below data</p>
<div class=""s-table-container"">
<table class=""s-table"">
<thead>
<tr>
<th style=""text-align: center;"">RESOURCE NAME</th>
<th style=""text-align: center;"">SKILL GROUP</th>
<th style=""text-align: center;"">PROJECT</th>
<th style=""text-align: center;"">COST   PER HOUR</th>
<th style=""text-align: center;"">CAPACITY</th>
</tr>
</thead>
<tbody>
<tr>
<td style=""text-align: center;"">Resource 1</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 3</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   4</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">200.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 5</td>
<td style=""text-align: center;"">CICD and Devops</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   6</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 7</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">10.0</td>
<td style=""text-align: center;"">250.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   8</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 9</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
</tbody>
</table>
</div>
<p>and my references table has got these entries</p>
<div class=""s-table-container"">
<table class=""s-table"">
<thead>
<tr>
<th style=""text-align: center;"">RESOURCE NAME</th>
<th style=""text-align: center;"">SKILL GROUP</th>
<th style=""text-align: center;"">PROJECT</th>
<th style=""text-align: center;"">DEMAND</th>
<th style=""text-align: center;"">Capacity</th>
</tr>
</thead>
<tbody>
<tr>
<td style=""text-align: center;"">Resource 1</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">900.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">300.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 3</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">400.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   1</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">200.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 4</td>
<td style=""text-align: center;"">CICD and Devops</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">300.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   5</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">900.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 6</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">200.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">900.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 7</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">400.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   8</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">800.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 9</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">900.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Pipeline Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">600.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">700.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">900.0</td>
<td style=""text-align: center;""></td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">250.0</td>
<td style=""text-align: center;""></td>
</tr>
</tbody>
</table>
</div>
<p>What I am trying to achieve here to distribute my capacity hours given in the master table to all the matching records in reference table.</p>
<p><strong>For example:</strong>
In master table for Resource 10 total capacity is 900 hours and he has been allocated to 2 different projects in reference table, so his capacity columns in the reference table will be updated and total capacity should be distributed to 450 each and in future if he get assigned more projects then his total capacity will be distributed among all the entries matches to Resource 10. Same for other matching Resources.</p>
",https://stackoverflow.com/questions/69280513/distributing-the-hours-from-master-table-to-all-the-matching-values-in-reference,"['excel', 'excel-formula', 'pivot-table', 'powerquery']",73,True,0,2
how to handle repeated entries coming in the pivot table &amp; power query to populate in excel dashboard,"<p>I have got two tables <strong>capacity</strong> and <strong>demand</strong></p>
<p><strong>Capacity table looks like this:</strong></p>
<div class=""s-table-container"">
<table class=""s-table"">
<thead>
<tr>
<th style=""text-align: center;"">RESOURCE NAME</th>
<th style=""text-align: center;"">SKILL GROUP</th>
<th style=""text-align: center;"">PROJECT</th>
<th style=""text-align: center;"">START DATE</th>
<th style=""text-align: center;"">END DATE</th>
<th style=""text-align: center;"">COST   PER HOUR</th>
<th style=""text-align: center;"">CAPACITY</th>
</tr>
</thead>
<tbody>
<tr>
<td style=""text-align: center;"">Resource 1</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 3</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   4</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">30-Apr-2022</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 5</td>
<td style=""text-align: center;"">CICD and Devops</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   6</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 7</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Nov-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">10.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   8</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 9</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Dec-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Dec-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Dec-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   12</td>
<td style=""text-align: center;"">Pipeline Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Dec-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">11.0</td>
<td style=""text-align: center;"">900.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 13</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Dec-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">12.0</td>
<td style=""text-align: center;"">800.0</td>
</tr>
</tbody>
</table>
</div>
<p><strong>Demand Table looks like this:</strong></p>
<div class=""s-table-container"">
<table class=""s-table"">
<thead>
<tr>
<th style=""text-align: center;"">RESOURCE NAME</th>
<th style=""text-align: center;"">SKILL GROUP</th>
<th style=""text-align: center;"">PROJECT</th>
<th style=""text-align: center;"">START DATE</th>
<th style=""text-align: center;"">END DATE</th>
<th style=""text-align: center;"">DEMAND</th>
</tr>
</thead>
<tbody>
<tr>
<td style=""text-align: center;"">Resource 1</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">25-Oct-2021</td>
<td style=""text-align: center;"">200.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">300.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 3</td>
<td style=""text-align: center;"">DB Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">400.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   1</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">200.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 4</td>
<td style=""text-align: center;"">CICD and Devops</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Mar-2022</td>
<td style=""text-align: center;"">300.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   5</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">25-Oct-2021</td>
<td style=""text-align: center;"">400.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 6</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">200.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   2</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">25-Oct-2021</td>
<td style=""text-align: center;"">300.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 7</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">400.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   8</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 9</td>
<td style=""text-align: center;"">Report Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">800.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Pipeline Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">600.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Cloud Testing</td>
<td style=""text-align: center;"">Project 3</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">700.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource   10</td>
<td style=""text-align: center;"">Performance Testing</td>
<td style=""text-align: center;"">Project 2</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">250.0</td>
</tr>
<tr>
<td style=""text-align: center;"">Resource 11</td>
<td style=""text-align: center;"">Automation Testing</td>
<td style=""text-align: center;"">Project 1</td>
<td style=""text-align: center;"">1-Oct-2021</td>
<td style=""text-align: center;"">31-Dec-2021</td>
<td style=""text-align: center;"">250.0</td>
</tr>
</tbody>
</table>
</div>
<p>I merged these two tables using power query on the basis of <strong>resource name</strong> and trying to generate the below pivot table.</p>
<p><strong>Pivot table screenshot</strong><br>
<a href=""https://i.stack.imgur.com/FhtJl.png"" rel=""nofollow noreferrer""><img src=""https://i.stack.imgur.com/FhtJl.png"" alt=""Pivot table screenshot"" /></a></p>
<p>My common field in both the table is <strong>&quot;Resource Name&quot;</strong>, what I am trying to build in pivot table which will be further used in my dashboard with slicer. Trying to build dashboard like this.</p>
<p><strong>Dashboard screenshot</strong><br>
<a href=""https://i.stack.imgur.com/2PmMp.png"" rel=""nofollow noreferrer""><img src=""https://i.stack.imgur.com/2PmMp.png"" alt=""Dashboard Screenshot"" /></a></p>
<p><strong>Challenging points:</strong></p>
<ul>
<li>Not able to capture the Capacity Hours and Total capacity Cost, Cost is getting repeated. Other values from <strong>Hours</strong> &amp; <strong>Cost</strong> sections are fine Demand hours &amp; Total Demand as per the pivot table above.</li>
</ul>
<p><strong>PowerQuery Merge Screenshot</strong><br></p>
<p><a href=""https://i.stack.imgur.com/HUMAl.png"" rel=""nofollow noreferrer""><img src=""https://i.stack.imgur.com/HUMAl.png"" alt=""Power Query Screenshot"" /></a></p>
<p><strong>FinalTable Screenshot</strong> <br></p>
<p><a href=""https://i.stack.imgur.com/ztj8g.png"" rel=""nofollow noreferrer""><img src=""https://i.stack.imgur.com/ztj8g.png"" alt=""FinalTable Screenshot"" /></a></p>
<p>After that I choose &quot;Close &amp; Load&quot; with option selected to &quot;Upload to data model&quot;</p>
<p><strong>Here is the final Table look like:</strong></p>
<div class=""s-table-container"">
<table class=""s-table"">
<thead>
<tr>
<th>RESOURCE NAME</th>
<th>SKILL GROUP</th>
<th>PROJECT</th>
<th>START DATE</th>
<th>END DATE</th>
<th>COST PER HOUR</th>
<th>CAPACITY</th>
<th>DemandTable.RESOURCE NAME</th>
<th>DemandTable.SKILL GROUP</th>
<th>DemandTable.PROJECT</th>
<th>DemandTable.DETAIL</th>
<th>DemandTable.START DATE</th>
<th>DemandTable.END DATE</th>
<th>DemandTable.DEMAND</th>
</tr>
</thead>
<tbody>
<tr>
<td>Resource 1</td>
<td>Automation   Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>12</td>
<td>800</td>
<td>Resource 1</td>
<td>Automation   Testing</td>
<td>Project 2</td>
<td></td>
<td>01-10-21 0:00</td>
<td>25-10-21 0:00</td>
<td>200</td>
</tr>
<tr>
<td>Resource   1</td>
<td>Automation Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>12</td>
<td>800</td>
<td>Resource 1</td>
<td>Report Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>200</td>
</tr>
<tr>
<td>Resource 2</td>
<td>DB Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>11</td>
<td>900</td>
<td>Resource 2</td>
<td>DB Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>300</td>
</tr>
<tr>
<td>Resource   2</td>
<td>DB Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>11</td>
<td>900</td>
<td>Resource 2</td>
<td>Cloud Testing</td>
<td>Project 2</td>
<td></td>
<td>01-10-21 0:00</td>
<td>25-10-21 0:00</td>
<td>300</td>
</tr>
<tr>
<td>Resource 3</td>
<td>DB Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>12</td>
<td>800</td>
<td>Resource 3</td>
<td>DB Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>400</td>
</tr>
<tr>
<td>Resource   4</td>
<td>Report Testing</td>
<td>Project 2</td>
<td>01-10-21 0:00</td>
<td>30-04-22 0:00</td>
<td>12</td>
<td>200</td>
<td>Resource 4</td>
<td>CICD and Devops</td>
<td>Project 3</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>300</td>
</tr>
<tr>
<td>Resource 5</td>
<td>CICD and Devops</td>
<td>Project 3</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>11</td>
<td>800</td>
<td>Resource 5</td>
<td>Performance   Testing</td>
<td>Project 2</td>
<td></td>
<td>01-10-21 0:00</td>
<td>25-10-21 0:00</td>
<td>400</td>
</tr>
<tr>
<td>Resource   6</td>
<td>Performance Testing</td>
<td>Project 1</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>12</td>
<td>900</td>
<td>Resource 6</td>
<td>Automation Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>200</td>
</tr>
<tr>
<td>Resource 7</td>
<td>Automation   Testing</td>
<td>Project 2</td>
<td>01-11-21 0:00</td>
<td>31-03-22 0:00</td>
<td>10</td>
<td>250</td>
<td>Resource 7</td>
<td>Report Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>400</td>
</tr>
<tr>
<td>Resource   8</td>
<td>Cloud Testing</td>
<td>Project 3</td>
<td>01-10-21 0:00</td>
<td>31-03-22 0:00</td>
<td>12</td>
<td>900</td>
<td>Resource 8</td>
<td>Cloud Testing</td>
<td>Project 3</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>800</td>
</tr>
<tr>
<td>Resource 9</td>
<td>Report Testing</td>
<td>Project 1</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>11</td>
<td>800</td>
<td>Resource 9</td>
<td>Report Testing</td>
<td>Project 2</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>800</td>
</tr>
<tr>
<td>Resource   10</td>
<td>Cloud Testing</td>
<td>Project 1</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>11</td>
<td>900</td>
<td>Resource 10</td>
<td>Pipeline Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>600</td>
</tr>
<tr>
<td>Resource 10</td>
<td>Cloud Testing</td>
<td>Project 1</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>11</td>
<td>900</td>
<td>Resource 10</td>
<td>Performance   Testing</td>
<td>Project 2</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>250</td>
</tr>
<tr>
<td>Resource   11</td>
<td>Report Testing</td>
<td>Project 3</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>12</td>
<td>800</td>
<td>Resource 11</td>
<td>Cloud Testing</td>
<td>Project 3</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>700</td>
</tr>
<tr>
<td>Resource 11</td>
<td>Report Testing</td>
<td>Project 3</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>12</td>
<td>800</td>
<td>Resource 11</td>
<td>Automation   Testing</td>
<td>Project 1</td>
<td></td>
<td>01-10-21 0:00</td>
<td>31-12-21 0:00</td>
<td>250</td>
</tr>
<tr>
<td>Resource   12</td>
<td>Pipeline Testing</td>
<td>Project 1</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>11</td>
<td>900</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>Resource 13</td>
<td>Cloud Testing</td>
<td>Project 3</td>
<td>01-12-21 0:00</td>
<td>31-12-21 0:00</td>
<td>12</td>
<td>800</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</tbody>
</table>
</div>
<p><strong>Queries:</strong> <br>
<strong>CapacityTable</strong></p>
<p><code>    let Source = Excel.CurrentWorkbook(){[Name=&quot;CapacityTable&quot;]}[Content], #&quot;Changed Type&quot; = Table.TransformColumnTypes(Source,{{&quot;RESOURCE NAME&quot;, type text}, {&quot;SKILL GROUP&quot;, type text}, {&quot;PROJECT&quot;, type text}, {&quot;START DATE&quot;, type datetime}, {&quot;END DATE&quot;, type datetime}, {&quot;SUN&quot;, type any}, {&quot;MON&quot;, type number}, {&quot;TUE&quot;, type number}, {&quot;WED&quot;, type number}, {&quot;THU&quot;, type number}, {&quot;FRI&quot;, type number}, {&quot;SAT&quot;, type any}, {&quot;COST PER HOUR&quot;, Int64.Type}, {&quot;CAPACITY&quot;, Int64.Type}}) in #&quot;Changed Type&quot;</code></p>
<p><strong>DemandTable</strong></p>
<p><code>let Source = Excel.CurrentWorkbook(){[Name=&quot;DemandTable&quot;]}[Content], #&quot;Changed Type&quot; = Table.TransformColumnTypes(Source,{{&quot;RESOURCE NAME&quot;, type text}, {&quot;SKILL GROUP&quot;, type text}, {&quot;PROJECT&quot;, type text}, {&quot;DETAIL&quot;, type any}, {&quot;START DATE&quot;, type datetime}, {&quot;END DATE&quot;, type datetime}, {&quot;SUN&quot;, type any}, {&quot;MON&quot;, type number}, {&quot;TUE&quot;, type number}, {&quot;WED&quot;, type number}, {&quot;THU&quot;, type number}, {&quot;FRI&quot;, type number}, {&quot;SAT&quot;, type any}, {&quot;DEMAND&quot;, Int64.Type}}) in    #&quot;Changed Type&quot;</code></p>
<p><strong>FinalTable</strong></p>
<p><code> let Source = Table.NestedJoin(DemandTable, {&quot;RESOURCE NAME&quot;}, CapacityTable, {&quot;RESOURCE NAME&quot;}, &quot;CapacityTable&quot;, JoinKind.LeftOuter), #&quot;Expanded CapacityTable&quot; = Table.ExpandTableColumn(Source, &quot;CapacityTable&quot;, {&quot;RESOURCE NAME&quot;, &quot;SKILL GROUP&quot;, &quot;PROJECT&quot;, &quot;COST PER HOUR&quot;, &quot;CAPACITY&quot;}, {&quot;CapacityTable.RESOURCE NAME&quot;, &quot;CapacityTable.SKILL GROUP&quot;, &quot;CapacityTable.PROJECT&quot;, &quot;CapacityTable.COST PER HOUR&quot;, &quot;CapacityTable.CAPACITY&quot;}) in #&quot;Expanded CapacityTable&quot;</code></p>
<p>Problem is that when I am trying to build the data through pivot table for capacity vs demand for all my projects and resources, my capacity hours are getting repeated for each records which are present in my Demand Table. I believe I need to get my data populated on the basis of Project, however, not sure what need to be done.</p>
",https://stackoverflow.com/questions/69243997/how-to-handle-repeated-entries-coming-in-the-pivot-table-power-query-to-popula,"['excel', 'pivot-table', 'powerquery', 'dashboard']",149,True,0,1
