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

[robinwilson16]

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 when creating a Pivot Table using the wizard (Alt + D + P - is there no button anymore for this?) this works to correctly transpose the data but a pivot table always gains a header row with "Sum of Value" in cell A1. The effect of this is that when importing this table into PowerBI, the column headers say column 1, column 2, column 3, etc...

Is there any way to get Excel to now show this title row at the top so it does not mess up the import into Power BI?

I did find I could rename all the columns manually in Power BI and filter out the "Sum of Value" row but this isn't very futureproofed and I feel there must be a better way.

Thanks

Robin

[v-qiuyu-msft]

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]

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

[robinwilson16]

Hello

Can anyone confirm my method above is the best way to do this as I have to add some more pivots and don't want to have to re-do them all if there is a better approch I should be using.

Thanks

Robin

[robinwilson16]

I have now tried using the transpose function in Power BI and this almost works but doesn't transpose the column names to row values so all I have is the values with no indication of what the values relate to so I cannot use this method unless there is something I am missing as this somewhat limits the usefulness of a transpose function.