Combine two tables - PowerPivot

[sbawnh]

HI,

I have Table1 and Table2 in a power pivot data model. I can show all of Table 1, but only just "Cat" from Table2. Is there anyway to show Turtle when Table1 is the Primary?


Table1

Animal	January
Dog	1​
Cat	1​

Table2

Animal	July
Cat	1​
Turtle	1​

PowerPivot

Animal	January	July
Dog	1​	0​
Cat	1​	1​

What I want

Animal	January	July
Dog	1​	0​
Cat	1​	1​
Turtle	0​	1​

 

[Kamolga]

I would upload both tables to power query,append them (then group by Animal if you want 2 cats in January)

 

[jdellasala]

Create the two tables:

Book1
	A	B	C	D	E
1	Animal	January		Animal	July
2	Dog	1		Cat	1
3	Cat	1		Turtle	1
Sheet1

And bring them both into Power Query - right click anywhere in each table and select Get Data from Table/Range. Load them both as Connection Only.
From the Data menu select Get Data -> Combine Queries -> Append. That will create this table in Power Query - shown here for information:

Book1
	L	M	N
1	Animal	January	July
2	Dog	1
3	Cat	1
4	Cat		1
5	Turtle		1
Sheet1

The problem is that the data isn't laid out properly. In the Power Query editor, right click on the Animal and select Unpivoted Other Columns. That will result in a table like this:

Book1
	L	M	N
1	Animal	Attribute	Value
2	Dog	January	1
3	Cat	January	1
4	Cat	July	1
5	Turtle	July	1
Sheet1

You can easily rename the columns in the Power Query editor to something like Month and Count.
After unpivoting and renaming the columns, Load To a PivotTable Report.
Put the Animal column into Rows, Month into Columns, and Count into Values. That will give you the Pivot Table you're looking for:

Book1
	G	H	I	J
1	Sum of Count	Column Labels
2	Row Labels	January	July	Grand Total
3	Cat	1	1	2
4	Dog	1	0	1
5	Turtle	0	1	1
6	Grand Total	2	2	4
Sheet1

The only significant change I made was to the Pivot Table Options which was to specify displaying 0 for empty cells.

 

[sbawnh]

Thank you!!! I really appreciate this.