How to Transpose header row value group by particular column value

Question

I have some data in excel in following format:

Cust_Name    Prod1     Prod2    Prod3     Prod4    Prod5
A            0         100      120       0        0
B            145       120      168       0        200
C            350       300      0        340       0

I need to convert the following format in below-mentioned report format.

I want to transpose those product in a column group by Cust_Name which has value >0 else it shouldn't be part of final output report.

I have tried many pivot option but it didn't work.

Required Output:

Cust_Name      Product       Price
A              Prod2         100
               Prod3         120
Total A        -             220
B              Prod1         145
               Prod2         120
               Prod3         168
               Prod5         200
Total B        -             633
C              Prod1         350
               Prod2         300
               Prod4         340
Total C        -             890

Answer 1

You can do this with PowerQuery.

Select any cell in your source data. Use Data>Get & Transform Data>From Table/Range.

The PowerQuery editor will open, like this:

Select the Cust_Name column by clicking the column header. Use Transform>Unpivot Columns>Unpivot Other Columns:

At this point, optionally filter the Value column to exclude 0.

Now use Home>Close & Load to put the data back into your workbook.

You can now create a pivot table to get your sub totals:

Here is the query from the Advanced Editor dialog in the PowerQuery editor:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust_Name", type text}, {"Prod1", Int64.Type}, {"Prod2", Int64.Type}, {"Prod3", Int64.Type}, {"Prod4", Int64.Type}, {"Prod5", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Cust_Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"