Refreshing Data in a Pivot Table

[Andy0311]

Hello,
In the table below, I added a column April

I then go to my Pivot Table, click into the table, right click, select Refresh, and expect to get the new column incorporated properly into my table, but instead get this:

Another row April has been added. I thought it was the placement of my cursor, but I don't get the result I want with different cursor placements. I can't figure out what I am doing wrong. Any assistance would be greatly appreciated. Thank you. A

 

[sandy666]

first, this is not a Pivot Table but Query Table
second, post your M code using code tags: [CODE=pq] your code here [/CODE]

Staff	Class	Jan	Feb	Mar	Apr		Staff	Class	Month	Lessons
Bob	Math	40	45	50	60		Bob	Math	Jan	40
Tom	PE	40	45	50	60		Bob	Math	Feb	45
Brian	SS	40	45	50	60		Bob	Math	Mar	50
Aimee	English	40	45	50	60		Bob	Math	Apr	60
							Tom	PE	Jan	40
							Tom	PE	Feb	45
							Tom	PE	Mar	50
							Tom	PE	Apr	60
							Brian	SS	Jan	40
							Brian	SS	Feb	45
							Brian	SS	Mar	50
							Brian	SS	Apr	60
							Aimee	English	Jan	40
							Aimee	English	Feb	45
							Aimee	English	Mar	50
							Aimee	English	Apr	60

 

[Andy0311]

I don't know what this (below) means, but thank you for responding.
"second, post your M code using code tags:

 your code here

"

 

[sandy666]

type [CODE=pq]
paste your code from Advanced Editor here
then type this [/CODE]

 

[Andy0311]

= Table.RenameColumns(#"Unpivoted Only Selected Columns",{{"Attribute", "Month"}, {"Value", "Lessons"}})

I hope this is what you meant in your message. Thanks again.

 

[sandy666]

this is not whole code, I said open Advanced Editor , copy code (whole code) and paste here
but as I can see you used the wrong function
should be: UnpivotOtherColumns

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Staff", "Class"}, "Month", "Lessons")
in
    Unpivot

 

[Andy0311]

Thanks for all your help. I started from the beginning and easily created my query table and updated it. I appreciate it. A

 

[sandy666]

You are welcome

it's nice to see if someone appreciate your work so you can hit Like in the posts which have helped you

btw. Pivot Table from Query Table looks like

Staff	Class	Month	Sum of Lessons
Aimee	English	Jan	40
		Feb	45
		Mar	50
		Apr	60
Bob	Math	Jan	40
		Feb	45
		Mar	50
		Apr	60
Brian	SS	Jan	40
		Feb	45
		Mar	50
		Apr	60
Tom	PE	Jan	40
		Feb	45
		Mar	50
		Apr	60

 

[Andy0311]

Thanks, Sandy! That will be one way I will use it to report the data.

 

[sandy666]

Glad to help ?