Pivot Table creating new data source every refresh

[Excel_117]

Just like the title says I have a pivot table linked to a dynamic data source (sql query) that changes in range size on sheet1 and on sheet2 a pivot table that is just used to format the same data with subtotals and totals etc.

every time I refresh the pivot table it creates a new data source "CompanyData2" witht the number incrementing and the data won't change on the pivot table cause obviously now it's linked to the new data source which is totally different. Is there a fix for this?

Thanks!

[#2]

It's not clear how that SQL query updates the source data--so maybe you could flesh that out a bit.

Specifically, does ALL of the data change each time?
Do the columns involved change?

I'm going to assume that when you say the "range size" changes, you mean that the number of rows change, but that the columns stay the same. I'm further going to assume that essentially you're adding new data (new transactions with new dates, or new something or others with new measurements associated)...
If those assumptions are accurate, let me ask if the data set is established as an Excel Table? If it is, and if it could be clear that you're just adding rows at the bottom, then it should be possible to just hit "Refresh Data" and have the Pivot Table work with a consistent name for the data source.

So tell me (along with anybody else reading this) how your situation differs from those things I asked about and assumed.

[#3]

@mathetes 

okay I attached a screenshot of the data sources, picture 1 is the datapage and pivot table is the pivot table, when I first created the pivot table I told it to get the data from the existing CompanyDataNew table and when I refresh it makes it own with a new number and the other one doesn't do that.

[#4]

Unfortunately, those images show the SQL, but don't really address the questions and assumptions I made. If all this is about Power Query, I am not in a position to offer help. My questions and assumptions had to do with the Excel portions of your situation (apart from Power Query). If you care to try again, please do. Otherwise, we'll have to await somebody else with Power Query experience.