CODEWITHSUNDEEP
excelpivot-tablepowerquerydashboard
techmaster
techmaster

Reputation: 69

how to handle repeated entries coming in the pivot table & power query to populate in excel dashboard

I have got two tables capacity and demand

Capacity table looks like this:

RESOURCE NAME SKILL GROUP PROJECT START DATE END DATE COST PER HOUR CAPACITY
Resource 1 Automation Testing Project 1 1-Oct-2021 31-Mar-2022 12.0 800.0
Resource 2 DB Testing Project 1 1-Oct-2021 31-Mar-2022 11.0 900.0
Resource 3 DB Testing Project 1 1-Oct-2021 31-Dec-2021 12.0 800.0
Resource 4 Report Testing Project 2 1-Oct-2021 30-Apr-2022 12.0 900.0
Resource 5 CICD and Devops Project 3 1-Oct-2021 31-Mar-2022 11.0 800.0
Resource 6 Performance Testing Project 1 1-Oct-2021 31-Mar-2022 12.0 900.0
Resource 7 Automation Testing Project 2 1-Nov-2021 31-Mar-2022 10.0 800.0
Resource 8 Cloud Testing Project 3 1-Oct-2021 31-Mar-2022 12.0 900.0
Resource 9 Report Testing Project 1 1-Dec-2021 31-Dec-2021 11.0 800.0
Resource 10 Cloud Testing Project 1 1-Dec-2021 31-Dec-2021 11.0 900.0
Resource 11 Report Testing Project 3 1-Dec-2021 31-Dec-2021 12.0 800.0
Resource 12 Pipeline Testing Project 1 1-Dec-2021 31-Dec-2021 11.0 900.0
Resource 13 Cloud Testing Project 3 1-Dec-2021 31-Dec-2021 12.0 800.0

Demand Table looks like this:

RESOURCE NAME SKILL GROUP PROJECT START DATE END DATE DEMAND
Resource 1 Automation Testing Project 2 1-Oct-2021 25-Oct-2021 200.0
Resource 2 DB Testing Project 1 1-Oct-2021 31-Dec-2021 300.0
Resource 3 DB Testing Project 1 1-Oct-2021 31-Dec-2021 400.0
Resource 1 Report Testing Project 1 1-Oct-2021 31-Dec-2021 200.0
Resource 4 CICD and Devops Project 3 1-Oct-2021 31-Mar-2022 300.0
Resource 5 Performance Testing Project 2 1-Oct-2021 25-Oct-2021 400.0
Resource 6 Automation Testing Project 1 1-Oct-2021 31-Dec-2021 200.0
Resource 2 Cloud Testing Project 2 1-Oct-2021 25-Oct-2021 300.0
Resource 7 Report Testing Project 1 1-Oct-2021 31-Dec-2021 400.0
Resource 8 Cloud Testing Project 3 1-Oct-2021 31-Dec-2021 800.0
Resource 9 Report Testing Project 2 1-Oct-2021 31-Dec-2021 800.0
Resource 10 Pipeline Testing Project 1 1-Oct-2021 31-Dec-2021 600.0
Resource 11 Cloud Testing Project 3 1-Oct-2021 31-Dec-2021 700.0
Resource 10 Performance Testing Project 2 1-Oct-2021 31-Dec-2021 250.0
Resource 11 Automation Testing Project 1 1-Oct-2021 31-Dec-2021 250.0

I merged these two tables using power query on the basis of resource name and trying to generate the below pivot table.

Pivot table screenshot
Pivot table screenshot

My common field in both the table is "Resource Name", 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.

Dashboard screenshot
Dashboard Screenshot

Challenging points:

PowerQuery Merge Screenshot

Power Query Screenshot

FinalTable Screenshot

FinalTable Screenshot

After that I choose "Close & Load" with option selected to "Upload to data model"

Here is the final Table look like:

RESOURCE NAME SKILL GROUP PROJECT START DATE END DATE COST PER HOUR CAPACITY DemandTable.RESOURCE NAME DemandTable.SKILL GROUP DemandTable.PROJECT DemandTable.DETAIL DemandTable.START DATE DemandTable.END DATE DemandTable.DEMAND
Resource 1 Automation Testing Project 1 01-10-21 0:00 31-03-22 0:00 12 800 Resource 1 Automation Testing Project 2 01-10-21 0:00 25-10-21 0:00 200
Resource 1 Automation Testing Project 1 01-10-21 0:00 31-03-22 0:00 12 800 Resource 1 Report Testing Project 1 01-10-21 0:00 31-12-21 0:00 200
Resource 2 DB Testing Project 1 01-10-21 0:00 31-03-22 0:00 11 900 Resource 2 DB Testing Project 1 01-10-21 0:00 31-12-21 0:00 300
Resource 2 DB Testing Project 1 01-10-21 0:00 31-03-22 0:00 11 900 Resource 2 Cloud Testing Project 2 01-10-21 0:00 25-10-21 0:00 300
Resource 3 DB Testing Project 1 01-10-21 0:00 31-12-21 0:00 12 800 Resource 3 DB Testing Project 1 01-10-21 0:00 31-12-21 0:00 400
Resource 4 Report Testing Project 2 01-10-21 0:00 30-04-22 0:00 12 200 Resource 4 CICD and Devops Project 3 01-10-21 0:00 31-03-22 0:00 300
Resource 5 CICD and Devops Project 3 01-10-21 0:00 31-03-22 0:00 11 800 Resource 5 Performance Testing Project 2 01-10-21 0:00 25-10-21 0:00 400
Resource 6 Performance Testing Project 1 01-10-21 0:00 31-03-22 0:00 12 900 Resource 6 Automation Testing Project 1 01-10-21 0:00 31-12-21 0:00 200
Resource 7 Automation Testing Project 2 01-11-21 0:00 31-03-22 0:00 10 250 Resource 7 Report Testing Project 1 01-10-21 0:00 31-12-21 0:00 400
Resource 8 Cloud Testing Project 3 01-10-21 0:00 31-03-22 0:00 12 900 Resource 8 Cloud Testing Project 3 01-10-21 0:00 31-12-21 0:00 800
Resource 9 Report Testing Project 1 01-12-21 0:00 31-12-21 0:00 11 800 Resource 9 Report Testing Project 2 01-10-21 0:00 31-12-21 0:00 800
Resource 10 Cloud Testing Project 1 01-12-21 0:00 31-12-21 0:00 11 900 Resource 10 Pipeline Testing Project 1 01-10-21 0:00 31-12-21 0:00 600
Resource 10 Cloud Testing Project 1 01-12-21 0:00 31-12-21 0:00 11 900 Resource 10 Performance Testing Project 2 01-10-21 0:00 31-12-21 0:00 250
Resource 11 Report Testing Project 3 01-12-21 0:00 31-12-21 0:00 12 800 Resource 11 Cloud Testing Project 3 01-10-21 0:00 31-12-21 0:00 700
Resource 11 Report Testing Project 3 01-12-21 0:00 31-12-21 0:00 12 800 Resource 11 Automation Testing Project 1 01-10-21 0:00 31-12-21 0:00 250
Resource 12 Pipeline Testing Project 1 01-12-21 0:00 31-12-21 0:00 11 900
Resource 13 Cloud Testing Project 3 01-12-21 0:00 31-12-21 0:00 12 800

Queries:
CapacityTable

let Source = Excel.CurrentWorkbook(){[Name="CapacityTable"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"RESOURCE NAME", type text}, {"SKILL GROUP", type text}, {"PROJECT", type text}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"SUN", type any}, {"MON", type number}, {"TUE", type number}, {"WED", type number}, {"THU", type number}, {"FRI", type number}, {"SAT", type any}, {"COST PER HOUR", Int64.Type}, {"CAPACITY", Int64.Type}}) in #"Changed Type"

DemandTable

let Source = Excel.CurrentWorkbook(){[Name="DemandTable"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"RESOURCE NAME", type text}, {"SKILL GROUP", type text}, {"PROJECT", type text}, {"DETAIL", type any}, {"START DATE", type datetime}, {"END DATE", type datetime}, {"SUN", type any}, {"MON", type number}, {"TUE", type number}, {"WED", type number}, {"THU", type number}, {"FRI", type number}, {"SAT", type any}, {"DEMAND", Int64.Type}}) in #"Changed Type"

FinalTable

let Source = Table.NestedJoin(DemandTable, {"RESOURCE NAME"}, CapacityTable, {"RESOURCE NAME"}, "CapacityTable", JoinKind.LeftOuter), #"Expanded CapacityTable" = Table.ExpandTableColumn(Source, "CapacityTable", {"RESOURCE NAME", "SKILL GROUP", "PROJECT", "COST PER HOUR", "CAPACITY"}, {"CapacityTable.RESOURCE NAME", "CapacityTable.SKILL GROUP", "CapacityTable.PROJECT", "CapacityTable.COST PER HOUR", "CapacityTable.CAPACITY"}) in #"Expanded CapacityTable"

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.

Upvotes: 0

Views: 168

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

To create the Pivot Table you are showing, but without duplicating data, you can:

  • Join the two tables based on {"RESOURCE NAME","PROJECT"} using JoinKind.FullOuter
  • Expand the table and "fill in the nulls" in the PROJECT and RESOURCE NAME columns for the rows that are missing an entry from either the Capacity or Demand tables
  • Add Demand*Cost column.
  • After cleaning things up, you can Save And Load To a Pivot table.

M Code

let

//Lodad and Type Capacity Table
    Source1 = Excel.CurrentWorkbook(){[Name="Capacity"]}[Content],
    Capacity = Table.TransformColumnTypes(Source1,{
        {"RESOURCE NAME", type text}, {"SKILL GROUP", type text}, {"PROJECT", type text}, 
        {"START DATE", type date}, {"END DATE", type date}, 
        {"COST PER HOUR", Currency.Type}, {"CAPACITY", Number.Type}}),

//Lodad and Type Demand Table    
    Source2 = Excel.CurrentWorkbook(){[Name="Demand"]}[Content],
    Demand = Table.TransformColumnTypes(Source2,{
        {"RESOURCE NAME", type text}, {"SKILL GROUP", type text}, {"PROJECT", type text}, 
        {"START DATE", type date}, {"END DATE", type date}, {"DEMAND", Number.Type}}),

//Join the two tables
    joined = Table.NestedJoin(Capacity,{"RESOURCE NAME","PROJECT"},Demand,{"RESOURCE NAME","PROJECT"},"Joined",JoinKind.FullOuter),

//Remove unneeded columns and expand the Joined table
    #"Removed Columns" = Table.RemoveColumns(joined,{"SKILL GROUP", "START DATE", "END DATE"}),
    #"Expanded Joined" = Table.ExpandTableColumn(#"Removed Columns", "Joined", 
        {"RESOURCE NAME", "PROJECT", "DEMAND"}, 
        {"Demand.RESOURCE NAME", "Demand.PROJECT", "Demand.DEMAND"}),

//Transform the null records for those missing from one table or the other
    capFN = {"RESOURCE NAME", "PROJECT", "COST PER HOUR","CAPACITY"},
    demFN = {"Demand.RESOURCE NAME", "Demand.PROJECT", "Demand.DEMAND"},
    recs = Table.ToRecords(#"Expanded Joined"),
    xForm = List.Generate(
        ()=>[rec = recs{0}, idx=0],
        each [idx] < List.Count(recs),
        each [rec = if recs{[idx]+1}[RESOURCE NAME] = null or recs{[idx]+1}[Demand.RESOURCE NAME]= null then 
            let 
                rtl = Record.ToList(recs{[idx]+1}),
                xRtl = if rtl{0} = null then List.ReplaceRange(rtl,0,2, List.Range(rtl,4,2)) 
                    else List.ReplaceRange(rtl,4,2, List.Range(rtl,0,2)) 
            in Record.FromList(xRtl, List.Combine({capFN,demFN}))
            
                        
                        else recs{[idx]+1}, idx=[idx]+1],
        each [rec]),
    #"Converted to Table" = Table.FromList(xForm, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", 
        {"RESOURCE NAME", "PROJECT", "COST PER HOUR", "CAPACITY", "Demand.DEMAND"}, 
        {"RESOURCE NAME", "PROJECT", "COST PER HOUR", "CAPACITY", "Demand.DEMAND"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{
        {"RESOURCE NAME", type text}, {"PROJECT", type text}, 
        {"COST PER HOUR", Currency.Type}, 
        {"CAPACITY", Int64.Type}, {"Demand.DEMAND", Int64.Type}}),

//Add demand*cost column
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Demand Cost", each [COST PER HOUR]*[Demand.DEMAND], Currency.Type),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"COST PER HOUR"})
in
    #"Removed Columns1"

=>

enter image description here

Upvotes: 1

Related Questions