Search code examples
excelpowerbipivotpowerquerypowerpivot

Excel Power Pivot / Power Query sum Milestones per Quarter per Type in a Pivot Table

I have a data model from importing data from multiple excel sheets via Power Query and I am generating Measures in Power Pivot for the count of Type. My data table looks like the left table in the following image and I want to transform it into a Pivot table to display the sum of the types for each milestone per quarter.

Table and desired Pivot Table Output

I tried to set up a quarter table and link it to the left table but i am not able to link it to multiple columns (as the quarters are referenced in each milestone column). Is here a way to get achieve this result in Power Pivot or Power Query and display it in a way as seen on the right side of the image?

Solution

  • Here is a Power Query option that unpivots 'Milestone' columns to produce a table conducive to generating an appropriate Pivot Table.

    Once the data is loaded into PowerQuery:

    1. Select the 'Type' Column
    2. Click 'Transform'
    3. Click the 'Unpivot Columns' drop down then 'Unpivot Other Columns'

    Unpivot Columns

    1. Add a count column by going to 'Add Column' -> 'Custom Column' and enter 1 in the pop up window.

    This should be the resulting table:

    Tidy Table

    Load the above resulting table to a PivotTable which results in the Pivot Table below (note: I renamed the 'Value' column to 'Period'):

    Pivot Table Result

    For the sake of reproducibility, here is the M Code:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"Milestone 1", type text}, {"Milestone 2", type text}, {"Milestone 3", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Type"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Value", "Period"}}),
    #"Add Count Column" = Table.AddColumn(#"Renamed Columns", "Count", each 1, Int64.Type)
in
    #"Add Count Column"