EN VI

How to create Excel Pivot Table from a table with hierarchical columns?

In Excel spreadsheet, I have a table with columns: "Epic" and "Stories" and "Tasks". There is another column to enter hours for each task:

| Code | Epic | Stories | Task | Hour |
| 1    | E1   |         |      |      |
| 1.1  |      | St1     |      |      |
| 1.1.1|      |         | Ts1  |10    |
| 1.1.2|      |         | Ts2  |20    |
| 1.2  |      | St2     |      |      |
| 1.2.1|      |         | Ts21 |5     |
| 1.2.3|      |         | Ts22 |2     |
| 2    | E2   |         |      |      |
| 2.1  |      |         |      |      |

Now I want to create a pivot table to show the sum for each epic. For example:

| Code | Epic | Hours | 
| 1    | E1   | 37    | 
| 2    | E2   | xyz   | 
|

How do I achieve this?

Solution:

There are many of ways of achieving the desired outputs these days, if you are using Modern Versions of Excel, one such way is using Power Query or Newer Excel Functions, which makes it relatively easy.

• Using GROUPBY() if applicable -> works with MS365 Exclusively ^{Beta Version}

• Formula used in cell G1

=VSTACK({"Code","Epic","Hours"},
 GROUPBY(HSTACK(TEXTBEFORE(A2:A10&".","."),
 SCAN(,B2:B10,LAMBDA(x,y,IF(y="",x,y)))),E2:E10,SUM,,0))

Or, If one don't have access to GROUPBY() while writing the formula then needs to enable the Office Insiders, if you are reluctant to enable then could try the following as well:

=LET(
     _Data, A2:E10,
     _Code, TEXTBEFORE(TAKE(_Data,,1)&".","."),
     _Epic, SCAN(,INDEX(_Data,,2),LAMBDA(x,y,IF(y="",x,y))),
     _Uniq, UNIQUE(HSTACK(_Code,_Epic)),
     HSTACK(_Uniq, TAKE(MAP(_Uniq,LAMBDA(x,SUM(--(x=HSTACK(_Code,_Epic))*TAKE(_Data,,-1)))),,1)))

• Or Using POWER QUERY, available in Windows Excel 2010+ and Excel 365 (Windows) --> It is one time approach, hence you dont have to increase range or ranges, only when you add new data to the original source have to refresh the imported table, and it gets updated within few.

To use Power Query follow the steps:

First convert the source range into a table and name it accordingly, for this example I have named it as Table1.

Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query

The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Epic"}),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filled Down", {{"Code", each Text.BeforeDelimiter(Text.From(_, "en-US"), "."), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text Before Delimiter", {"Code", "Epic"}, {{"Hours", each List.Sum([Hour]), type nullable number}})
in
    #"Grouped Rows"

Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.