Power Query - Merging Tables only shows first table data

[jaspalsd]

Hi,

I have searched online tutorials and I am stumped as to why I can't merge two tables in Power query.

I have therefore created a very simple example

ANIMAL	COUNT	ID
DOG	1​	2​
CAT	5​	1​

ANIMAL	COUNT	ID
CAT	6​	1​
MONKEY	3​	4​

If I do the the follow I will get these results:
Get Data > Combine queries > Merge > Table 1 (Select ID) and Table 2 (Select ID) > Full Outer Join


=Table.NestedJoin(Table1, {"ID"}, Table2, {"ID"}, "Table2", JoinKind.FullOuter)

ANIMAL	COUNT	ID	Table2
CAT	5	1

null

null

null

DOG

1

2

Can anyone please advise why joining on the Unique Identifier and performing a full outer join is returning the results above.


Thanks,

Jas

 

[alansidman]

When I do a full outer join as you describe and expand the table, here are the results.

ANIMAL	COUNT	ID		ANIMAL	Count	ID		ANIMAL	COUNT	ID	ANIMAL.1	Count.1	ID.1
DOG	1	2		CAT	6	1		CAT	5	1	CAT	6	1
CAT	5	1		MONKEY	3	4					MONKEY	3	4
								DOG	1	2

The Mcode for the full outer join are as follows

let
    Source = Table.NestedJoin(Table1, {"ID"}, Table2, {"ID"}, "Table2", JoinKind.FullOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ANIMAL", "Count", "ID"}, {"ANIMAL.1", "Count.1", "ID.1"})
in
    #"Expanded Table2"

You need to expand the table by double clicking on the double arrows in the upper right corner.

 

[jaspalsd]

Hi,

Thanks for the response. I might be a dummy here, but I am hoping to see the end result where the count is summarized as a total.

I could append but then again, it'll be on different rows e.g.

ID	ANIMAL	COUNT
2​	DOG	1​
1​	CAT	5​
1​	CAT	6​
4​	MONKEY	3​

However, I do not wish to pivot because my pivot has multiple months and I wish to avoid having to select all months manually. I will say there are 150 approx months, hence why I wish to avoid that.

Thank you

 

[RoryA]

It sounds like you want to append and then group.

 

[jaspalsd]

It sounds like you want to append and then group.

so to give further clarity, I was provided with 2 extracts but in different month ranges so I had to insert months with no data and have these with a sum of 0

Extract 1: Mar20 to Oct21
Extract 2: Nov21 to Jan23

For below, lets say Extract 1: Mar-20
Extract 2: Apr-20

So I added in the missing months in both extracts so that the columns were consistent

I then appended. If I pivot then the months will need to be added in manually and also they show in alphabetical order and it'll then have a heading SUM Apr-20.


On your point of grouping, I can see that option, so I can group by Agreement and Schedule (an agreement may have more than 1 schedule) but my concern is what I want to sum is 154 months...

Agreement

Schedule

Product Code

Customer

Introducer

CHANNEL

Asset Type Description

Asset Description

Start Date

End Date

Variable Deal

Mar-20

Apr-20

ABC1

1

1

dummy

dummy

dummy

dummy

dummy

06-Sep-18​

06-Sep-21​

dummy

50​

0.00​

Agreement

Schedule

Product Code

Customer

Introducer

CHANNEL

Asset Type Description

Asset Description

Start Date

End Date

Variable Deal

Mar-20

Apr-20

ABC1

1

dummy

dummy

dummy

dummy

dummy

dummy

06-Sep-18​

06-Sep-21​

dummy

0.00​

50.00​

 

[RoryA]

You should unpivot the extracts so that the months are in rows, not separate columns, then append and then pivot as the last step. Then you wouldn't need to add missing months or add up 154 columns.

 

[jaspalsd]

You should unpivot the extracts so that the months are in rows, not separate columns, then append and then pivot as the last step. Then you wouldn't need to add missing months or add up 154 columns.

hi, it was how the reports were given to me and I can see why because there is a potential for the CSVs to go over the 1m row limit (agreement, account types, each month for almost 15 years) ...I think I'll just append the data in table format and then perform sumifs on another worksheet

 

[RoryA]

I meant unpivot them in PQ. The unpivoted data doesn't need to be loaded to a worksheet.

 

[jaspalsd]

sorry if I am confusing you.

The extracts were from SQL Developer and then extracted to CSV format and never pivoted.

If you mean there is a way to change the format where the months are in the columns but I am able to put these into the rows along with the values then are you able to advise.

Thanks

 

[RoryA]

I think we've gone so far away from the example data that you posted, that I'm not really sure what your source data actually looks like, or what your end goal is?