如何处理数据透视表中的重复条目以及如何在 Excel 仪表板中填充查询

我有两张桌子capacity and demand

容量表如下所示：

需求表如下所示：

我使用 power query 合并了这两个表资源名称并尝试生成下面的数据透视表。

Pivot table screenshot

我在两个表中的共同字段是“资源名称”，我正在尝试在数据透视表中构建什么，该数据将在带有切片器的仪表板中进一步使用。尝试构建这样的仪表板。

Dashboard screenshot

挑战点：

• 无法捕获产能小时数和总产能成本，成本不断重复。其他值来自Hours & Cost根据上面的数据透视表，部分是很好的需求时间和总需求。

Power Query 合并屏幕截图

决赛桌截图

之后，我选择“关闭并加载”，并选择“上传到数据模型”选项

最终表格如下：

Queries:
容量表

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"

需求表

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"

决赛桌

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"

问题是，当我尝试通过数据透视表构建所有项目和资源的容量与需求的数据时，对于需求表中存在的每条记录，我的容量小时数都会重复。我相信我需要在项目的基础上填充我的数据，但是，不确定需要做什么。

要创建您正在显示的数据透视表，但不复制数据，您可以：

• 根据连接两个表{"RESOURCE NAME","PROJECT"} using JoinKind.FullOuter
• 展开表格并在其中“填充空值”PROJECT and RESOURCE NAME容量或需求表中缺少条目的行的列
• 添加需求*成本列。
• 清理完毕后，您可以保存并加载到数据透视表。

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"

=>