Forum Discussion

robinwilson16's avatar
robinwilson16
Frequent Visitor
9 years ago

Get Data From Excel Pivot Table - Issue with Sum of Value Row

Hello   I needed to display data as a table and a chart and had to convert the data from rows to columns to make the chart display the values correctly stacked which worked manually.   However wh...
Need Help
v-qiuyu-msft's avatar
v-qiuyu-msft
Icon for Community Support rankCommunity Support
9 years ago

Hi robinwilson16,

 

How did you create a Excel PivotTable? Is there only one PivotTable exists in the one sheet? any other table in this sheet?

 

Based on my test, if only one PivotTable exists in the sheet, import data to desktop, the columns will keep the same header as in the PivotTable. See:

 

 

In your scenario, would you clarify detail steps for us to create the PivotTable, so we can test it in our environment. If possible please share Excel files with us.

 

Best Regards,
Qiuyun Yu

robinwilson16's avatar
robinwilson16
Frequent Visitor
9 years ago

Hello Qiuyun

 

Thanks for your help and sorry for the delay in responding but I was not able to return to this project until this weekend.

 

I can confirm that there is only one pivot table in the tab I am trying to use.

 

The source data looks like this (I changed the values to enable me to share this):

 

 

The pivot looks like this and the problem is that I cannot get rid of the "Sum of Value" row at the top from within Excel:

 

 

However after some more experimentation I found from within Power BI I could filter out the Sum of Value row and then the header row was top so was able to select to use this a the header row for auto-naming the columns so I got it working in the end although not sure if this is the recommended solution:

 

Thanks

Robin