Hey community!
I am getting deeper and deeper into the world of Power Query and Power Pivot. It brings a lot of potential and makes my job a lot easier. Currently I am facing the following problem:I have the following two tables in Power Query, which I want to load in Power Pivot and present them as Pivot Table, etc .:
TblContracts
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Section[/TD]
[TD]Area[/TD]
[TD]Department[/TD]
[TD]Contracts[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA1[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA2[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]AA2[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]AA1[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]AB[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]5500[/TD]
[/TR]
[TR]
[TD]05.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]A1[/TD]
[TD]550[/TD]
[/TR]
</tbody>[/TABLE]
TblStaff
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Section[/TD]
[TD]Area[/TD]
[TD]Department[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]AA2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]AA1[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]AB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]05.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]A1[/TD]
[TD]
1[/TD]
[/TR]
</tbody>[/TABLE]
I now connect both tables using a calendar table and the date. Now I have to somehow connect the Sections, Areas and Departments to get a value from Contracts / Number of Employees at the end.
How is this implemented?
Using Hierarchies?
And, how can I upload a sample table here in Foum? I can not find an entry anywhere.
Do you need more information?
greetins
I am getting deeper and deeper into the world of Power Query and Power Pivot. It brings a lot of potential and makes my job a lot easier. Currently I am facing the following problem:I have the following two tables in Power Query, which I want to load in Power Pivot and present them as Pivot Table, etc .:
TblContracts
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Section[/TD]
[TD]Area[/TD]
[TD]Department[/TD]
[TD]Contracts[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA1[/TD]
[TD]900[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA2[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]AA2[/TD]
[TD]550[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]AA1[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]AB[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]5500[/TD]
[/TR]
[TR]
[TD]05.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]A1[/TD]
[TD]550[/TD]
[/TR]
</tbody>[/TABLE]
TblStaff
[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Section[/TD]
[TD]Area[/TD]
[TD]Department[/TD]
[TD]Staff[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA1[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]AA2[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]01.01.2018[/TD]
[TD]A[/TD]
[TD]2[/TD]
[TD]AA2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]AA1[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]B[/TD]
[TD]1[/TD]
[TD]AB[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]02.01.2018[/TD]
[TD]A[/TD]
[TD]3[/TD]
[TD]A[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]05.01.2018[/TD]
[TD]A[/TD]
[TD]1[/TD]
[TD]A1[/TD]
[TD]
1[/TD]
[/TR]
</tbody>[/TABLE]
I now connect both tables using a calendar table and the date. Now I have to somehow connect the Sections, Areas and Departments to get a value from Contracts / Number of Employees at the end.
How is this implemented?
Using Hierarchies?
And, how can I upload a sample table here in Foum? I can not find an entry anywhere.
Do you need more information?
greetins