help consolidating data required - pivot / power query

[lesfell]

Hello, I have a data with departments, region, item type and the number of items each employee requires per quarter. Then I need to be able to come up with the total number of items required per quarter and the total number of item per quarter per department. I have the sample data set in the attached file. I need to be able to come up with the required data as indicated in the second tab. I tried using pivot but I am having a hard time coming up with the data required. Can anyone help please? Thanks in advance!

 

[AlanSidman]

First Part

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Region", "Department"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "Yes" then [Value] else if [Value] = "No" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes") and ([Custom] = "Yes")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Custom", "Name", "Department", "Region"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum)
in
    #"Pivoted Column"

Second Part

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Name", "Region", "Department"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value] = "Yes" then [Value] else if [Value] = "No" then [Value] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Value] <> "No" and [Value] <> "Yes") and ([Custom] = "Yes")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Attribute.1", "Attribute.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Name", "Region", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute.2]), "Attribute.2", "Value", List.Sum),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"Attribute.1", "ItemType"}})
in
    #"Renamed Columns"

 

[pecoflyer]

Something like this ( with PQ)