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

 

[RoryA]

I'm not sure I follow but it sounds like you just need to load the second table as a query, merge it with the first and then filter for Germany.

 

[tigerzen]

I'm not sure I follow but it sounds like you just need to load the second table as a query, merge it with the first and then filter for Germany.

Hi Rory, I may not have explained it properly but each time I run the report I need PQ to update everything, I made all 4 records Germany in the new file so I could see the change easily. In the actual file the records will be similar so I wouldn't be able to just filter as I wouldn't know what to filter by.

 

[RoryA]

I'm afraid I don't really know whet you mean then. Which data changes and what is supposed to happen?

 

[tigerzen]

I'm afraid I don't really know whet you mean then. Which data changes and what is supposed to happen?

The desired output should look like:

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

Cell Formulas
Range	Formula
E2:E5	E2	=INDEX('Input 2'!$C$2:$C$13,MATCH(Data!A2,'Input 2'!$B$2:$B$13,0))

But the data comes to me without the index match part as per the third table in post 1

 

[RoryA]

Then you just need my original answer without the filtering part, as far as I can tell. You'd merge the two queries using the Name column as the link, and then expand the resulting table column to just return the country.

 

[tigerzen]

Then you just need my original answer without the filtering part, as far as I can tell. You'd merge the two queries using the Name column as the link, and then expand the resulting table column to just return the country.

Hi Rory, thanks for your assistance. I realise the highlighting may have been confusing. The output should be as per post 5 ie with Name, Gender etc.
I've been able to get the merged query to work for the initial issue which combines the first 2 tables as per post 1. I cannot see how I can now update the information when Table 3 from Post 1 comes in. Table 3 information replaces Table 1 but I need it to still link with Table 2, I hope that's a bit clearer.

 

[RoryA]

You need Table1 to be a query from somewhere, then update the source of that query with the new data.

 

[tigerzen]

You need Table1 to be a query from somewhere, then update the source of that query with the new data.

Not sure I follow that. Table 1 is already a query as it's used to create the connection between it and the Country table (2nd table in post 1). When you say from somewhere is there an extra step I need to perform?

 

[RoryA]

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.