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

 

[jaspalsd]

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

dummy

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​

The above examples are two csv extracts and never pivoted.

End goal, one line to look like this but I'll be having months from Mar-20 to Jan-23

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

50

50

 

[RoryA]

Then as I said earlier, I would probably unpivot the months fields in each source query, append the two queries, then repivot, summing the value fields for each month.

 

[jaspalsd]

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?

I'll try after I've googled it

 

[jdellasala]

You must make sure that your source tables are set up properly. That is, numbers are numbers and not text - which is why it's best to not format data in tables - numbers automatically justify right, and text automatically justify left. If the first table's CAT ID is "1" or '1 instead of 1, the merge of the two tables (assuming all the IDs in the 2nd table are numbers) is this:

Book1
	I	J	K	L	M	N
1	ANIMAL	COUNT	ID	Table2.ANIMAL	Table2.COUNT	Table2.ID
2	DOG	1	2
3	CAT	5	1
Sheet1

Simply correcting the CAT ID to be the NUMBER 1 instead of "1" results in this Merge:

Book1
	I	J	K	L	M	N
1	ANIMAL	COUNT	ID	Table2.ANIMAL	Table2.COUNT	Table2.ID
2	CAT	5	1	CAT	6	1
3	DOG	1	2
Sheet1

I purposely allowed the second table's name to preface the table's column name for clarity.
I would have posted the source tables using XL2BB, but unfortunately it doesn't preserve the justification I was trying to show. This is what the first merger's tables look like:

The table in columns A-C is Table1, the other is Table2. Hope that helps.

 

[jaspalsd]

Then as I said earlier, I would probably unpivot the months fields in each source query, append the two queries, then repivot, summing the value fields for each month.

Thanks, it worked but for one annoying thing.

Dates were in alphabetical order, e.g. I formatted dates in custom formatting for example, 01/04/20, 01/04/21 as APR-20, APR-21 so it is seen as text when I apply data within the initial table, So I didn't apply custom data to the table data but now it will appear in order as:

01/01/2020, 01/01/2021.......01/02/2020, 01/02/2021 and so on

I'm from the UK so my dates are defaulted like this. Any advice for resolving this last issue?

 

[RoryA]

How are you actually outputting the data - as a table or using pivot tables?

 

[jaspalsd]

How are you actually outputting the data - as a table or using pivot tables?

So initially I select the range of csv data and apply "From Table/Range" >and then it'll open up the Power Query editor for which I then create a connection.

However, the final output will be a pivot table.

 

[RoryA]

If the final output is a pivot table, then I wouldn't pivot in PQ as the final step. If you make the date field into a column header in the pivot, you can sort it there and format the cells however you like.

 

[jaspalsd]

I resolved the date issue by changing the data type on the Power query editor .

For the larger datasets I have approached this with Append (pivots take too long to return data). So all was well but I noticed that if I have an Example Reference 'ABC123' with a value of 14.45 in one of the tables but it is not in the second table, then the total sum of the reference will be rounded down to 14. I can see this within the Append view under the Query editor. The decimals will work where the references appears in both tables.

So the issue occurs when appending the data so changing the data type to decimal won't make a difference.

Is there a way to get round this?

 

[jaspalsd]

I resolved the date issue by changing the data type on the Power query editor .

For the larger datasets I have approached this with Append (pivots take too long to return data). So all was well but I noticed that if I have an Example Reference 'ABC123' with a value of 14.45 in one of the tables but it is not in the second table, then the total sum of the reference will be rounded down to 14. I can see this within the Append view under the Query editor. The decimals will work where the references appears in both tables.

So the issue occurs when appending the data so changing the data type to decimal won't make a difference.

Is there a way to get round this?

ignore me, figured out my mistake