Creating Relationship/Link among similar Tables in PowerPivot (Pivot Table)

[soupious]

Hi experts, I just start to use this awesome wonder called PowerPivot. I've been a regular user of Pivot Table. I am not sure if my question is a simple one. Here it goes. I have two Lookup Tables: Date Table and Factory Table (only consist of factory codes and their corresponding names). In the other hand, I have three Factory Production Tables. The columns I wanted to connect are the Total Production in each of the three Factory Production Tables. The reason I separated the three production tables is because each of them have slightly different calculation that would be to complex to combine into one table.


--------------------------------
These are the settings of my Pivot Table (from PowerPivot)
Row Label:
-Factory
-Year
-MonthName
-Day

Values:
-Sum of Total Production
-Sum of Total Production 2
-Sum of Total Production 3
--------------------------------

As a result, I will now have three separate columns giving me the Total Production. I do not know how to combine the three of the columns to be just one "MASTER-Sum of Total Production".

If (and only if) I could combine all three Production Tables (which almost impossible, it will be possible but with lots of blank columns as each of the factory has different calculated fields), all of this would have been solved already.

I have seen a video about Power BI ads. which says that it could combine all similar data, let say all total sales/production from similar types of reports of Multi National Company (where the reports are similar but in slightly different formatting due to preference of the staff in each country for example) into one Dashboard. I have not used Power BI yet but I know power BI is based on PowerPivot logical thinking. Please help me. I would greatly appreciate it. Have a good day!

 

[FranzV]

Hi Soupious,

if you are just starting to use Power Pivot, I strongly recommend you take a look at Power Query. It is a great feature by itself and the perfect companion for Power Pivot, helping you to shape data from a variety of sources prior to loading it to your data model.

For your particular situation, there is a very simple solution. I will try to explain using three dummy tables with different columns.

Excel 2016 (Windows) 64 bit

2​	01-feb​		20​
5​	01-feb​			50​
8​	01-feb​				80​

<tbody>
[TD="bgcolor: #5B9BD5"] Factory [/TD]
[TD="bgcolor: #5B9BD5"] Date [/TD]
[TD="bgcolor: #5B9BD5"] Calculation1 [/TD]
[TD="bgcolor: #5B9BD5"] Production [/TD]

[TD="bgcolor: #DDEBF7"]

1​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

10​

[/TD]

[TD="bgcolor: #DDEBF7"]

3​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

30​

[/TD]

[TD="bgcolor: #5B9BD5"] Factory [/TD]
[TD="bgcolor: #5B9BD5"] Date [/TD]
[TD="bgcolor: #5B9BD5"] Calculation1 [/TD]
[TD="bgcolor: #5B9BD5"] Calculation2 [/TD]
[TD="bgcolor: #5B9BD5"] Production [/TD]

[TD="bgcolor: #DDEBF7"]

4​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

40​

[/TD]

[TD="bgcolor: #DDEBF7"]

6​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

60​

[/TD]

[TD="bgcolor: #5B9BD5"] Factory [/TD]
[TD="bgcolor: #5B9BD5"] Date [/TD]
[TD="bgcolor: #5B9BD5"] Calculation1 [/TD]
[TD="bgcolor: #5B9BD5"] Calculation2 [/TD]
[TD="bgcolor: #5B9BD5"] Calculation3 [/TD]
[TD="bgcolor: #5B9BD5"] Production [/TD]

[TD="bgcolor: #DDEBF7"]

7​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

70​

[/TD]

[TD="bgcolor: #DDEBF7"]

9​

[/TD]
[TD="bgcolor: #DDEBF7"]

01-feb​

[/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

90​

[/TD]

</tbody>

Appending this tables doesn't seem so straightforward without having lots of columns with empty values for most rows, but Power Query comes to the rescue with some very simple steps to load a table with only the desired info from each table (Factory, Date, and Production).

1.- Select any cell within one of your tables and click 'From Table' in the 'Get & Transform' group of the 'Data' tab in the Menu Ribbon.

This will open the Power Query Editor window and you will see the data from your table without cell formatting. On the right side you will see a pane with the query steps (Source and Changed Type)

2.- Delete the 'Changed Type' step from the query (select the step and click on the X, hit Delete or right click and select Delete)

You shouldn't notice much of a change, except for the column headers and the contents of the formula bar that should now display something like:

= Excel.CurrentWorkbook(){[Name="YourTableName"]}[Content]

3.- Go to the formula bar and delete everything that comes after Excel.CurrentWorkbook() and hit ENTER. This should change the display to a table with two columns like:

[TABLE="class: head"]
<tbody>[TR]
[TD]Content[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD] Table[/TD]
[TD]Production03[/TD]
[/TR]
[TR]
[TD] Table[/TD]
[TD]Production02[/TD]
[/TR]
[TR]
[TD] Table[/TD]
[TD]Production01[/TD]
[/TR]
</tbody>[/TABLE]

4.- If you have other tables or named ranges in your workbook that don't belong to the production table, use the filter on the Name column to keep only you production tables.

5.- On the column header for Content, select the expand button ( something like ↰↱ ) and select the columns you need for PowerPivot using the checkboxes. Uncheck 'Use column name as prefix' and hit Ok.

You should now see a table with the columns you selected and all the data from the three tables.

6.- Select all the columns and choose 'Detect Data Type' (not sure of the exact word since my UI is in Spanish) in the 'Any Column' group of the 'Transform' tab of the Menu Ribbon.

7.- Now for the final step, click on the drop down menu in the 'Close' group of the 'Start' tab of the Menu Ribbon (leftmost button on the ribbon) and choose 'Close and Load In'. This should open a new window where you may choose between the Table and Connection Only options. I recommend Connection Only because it makes your file size smaller, but if you want to be able to access the newly created table directly from a worksheet you can select Table.

The important thing here is checking Add to Data Model to make the new table available to Power Pivot. Select Load.

You now have the table available for Pivot Tables and you can directly use the Production column or create a measure like SumProduction:= SUM( YourTable[Production] ) to display in your pivot table.

1​	2017​	febrero	1​	10​
2​	2017​	febrero	1​	20​
3​	2017​	febrero	1​	30​
4​	2017​	febrero	1​	40​
5​	2017​	febrero	1​	50​
6​	2017​	febrero	1​	60​
7​	2017​	febrero	1​	70​
8​	2017​	febrero	1​	80​
9​	2017​	febrero	1​	90​

<tbody>
[TD="bgcolor: #DDEBF7"] Factory [/TD]
[TD="bgcolor: #DDEBF7"] Year [/TD]
[TD="bgcolor: #DDEBF7"] Month [/TD]
[TD="bgcolor: #DDEBF7"] Day [/TD]
[TD="bgcolor: #DDEBF7"] SumProduction [/TD]

[TD="bgcolor: #DDEBF7"] Total general [/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"][/TD]
[TD="bgcolor: #DDEBF7"]

450​

[/TD]

</tbody>

Have fun. I hope it helps.

 

[soupious]

Hi FranzV,
Thanks for your help I will try to test it. My current company lacks of such resources where I do not have access to the latest Microsoft Office. They were using Office 2007 before I joined here. Now, they upgraded it to 2010, I do not think that Office 2010 has Power Query. Nevertheless, I appreciate your help, I will give it a try somehow. Thanks a bunch! Have a good day.

 

[citizenbh]

Power Query for Office 2010 (view System Requirements)

https://www.microsoft.com/en-us/download/details.aspx?id=39379

 

[soupious]

Hi citizenbh,
Wow. Alrighty, I will give it a try on Monday. Thanks!

Cheers

 

[citizenbh]

You're welcome!