pivot table from two datasources

[peterpaul]

Hello Excel-enthusiast,

I'm trying to use data, from two tables, in a pivot table. I made a relationship between de referece columns of table 1 & 2, but when I try to add the Weight column to the pivot table it sums the weights for every row? I just want to show the weight for every reference. Is this possible, if so , how?

Map3
	A	B	C	D	E	F	G	H
1	Table1			Table2
2	ID	Reference		Reference	Weight		Rijlabels	Som van Weight
3	911595	1		1	591,50		911595	1706,18
4	911755	5		4	865,08		1	1706,18
5	911755	2		5	249,60		4	1706,18
6	911595	4					911755	1706,18
7	953821	3					2	1706,18
8	946512	1					5	1706,18
9	953821	5					946512	1706,18
10							1	1706,18
11							953821	1706,18
12							3	1706,18
13							5	1706,18
14							Eindtotaal	1706,18
Blad1

 

[Alex Blakenburg]

In your tables Table1 is effectively the detail or fact table and Table2 is the Master Data. Pulling in a value from the Master just doesn't work using the Data Model.
To get what you seem to want pull both tables into Power Query as a connection only table and use Merge to create a single table that has ID, reference and weight.
The send it back to Excel either as a Table or a Pivot or to the Data model.