Updating data with Power Query when the updated data has an extra column and lookups are needed

[tigerzen]

I'm trying to use PQ to update a file which has a number of Pivot tables linked to the data. Basically it's the first table here which has a lookup to the second table. When the new data comes in, I then would like to replace the old data with it and have everything update accordingly, in this case the final update will only have Germany records.

PQ.xlsx
	A	B	C	D	E	F	G	H	I
1	Category	Name	Gender	Age	Dept
2	India	Tom	Male	36	Admin			Row Labels	Count of Name
3	Australia	Fred	Male	45	Sales			Admin	2
4	Italy	Mary	Female	55	HR			Finance	1
5	India	Sam	Male	23	Sales			HR	3
6	Australia	Jack	Male	55	Admin			Sales	2
7	France	Bert	Male	61	HR			Grand Total	8
8	Australia	Doris	Female	28	Finance
9	Italy	Nicola	Female	34	HR
Data

Cell Formulas
Range	Formula
A2:A9	A2	=INDEX(Category!$C$2:$C$9,MATCH(Data!B2,Category!$B$2:$B$9,0))

PQ.xlsx
	B	C
1	Name	Country
2	Tom	India
3	Fred	Australia
4	Mary	Italy
5	Sam	India
6	Jack	Australia
7	Bert	France
8	Doris	Australia
9	Nicola	Italy
10	Harry	Germany
11	Dan	Germany
12	Fiona	Germany
13	Noel	Germany
Category

Updated data

PQ_2.xlsx
	A	B	C	D
1	Name	Gender	Age	Dept
2	Harry	Male	36	Management
3	Dan	Male	55	Management
4	Fiona	Female	18	Executive
5	Noel	Male	60	HR
Sheet1

 

[tigerzen]

If Table1 is already a query, then you just need to replace its source data (that you merge with your country table) with the new data set and refresh it.

Thanks again Rory, ok so I tried that and it works although I have to refresh twice, once for the merged query and again for the linked pivot tables. I had thought that I could just do one global refresh but can't see how to do that. Also, replacing the source data seems more manual than what I'd like. I again thought that PQ could automate some of these manual steps, but copying and pasting over the original data in PQ is only slightly more efficient than what I'm manually doing now. It does get a bit tedious when you have to run many reports often.

 

[RoryA]

Where is the original source data? I generally prefer to keep that as a separate file that I just overwrite when I have new data (unless I need to keep a history).

I also prefer to build the pivot tables directly off the query rather than loading it to a table and then making the pivots off that, but since you're already set up that way, you should be able to just turn off background refresh for the query (uncheck the option below), then a RefreshAll should work:

 

[tigerzen]

Where is the original source data? I generally prefer to keep that as a separate file that I just overwrite when I have new data (unless I need to keep a history).

I also prefer to build the pivot tables directly off the query rather than loading it to a table and then making the pivots off that, but since you're already set up that way, you should be able to just turn off background refresh for the query (uncheck the option below), then a RefreshAll should work:

View attachment 118526

Still not quite working. I've made the changes as per your post but it's not updating the Country field. I'll keep trying and your suggestion of building the pivot tables directly off the query is a good one.