Pivot Table One Way Lookup

[pto160]

I am not sure if Pivot Tables are capable of this, or I need to use Power query or PowerPivot.
I have two tables. The common link between the tables is the transaction ID number. I want to compare table 1 to table 2 and not table 2 to table 1 so it is a one way lookup.
I want to pull in the month and settlement number from table 2 using the transaction ID and also the amounts. The type would be on the columns if I was using the pivot table format.
In this example, it shows transaction ID CD850, since that is in table 1 and not table 2. It does not show ZA123, as that is only in table 2 and not table 1.
Is this possible?

Book1
	A	B	C	D	E	F	G	H	I
1	Table 1
2	ID#	Cust ID	Cust Name	Invoice	Amount	Type
3	AB123	100	John Smith	1	50	GL Report
4	AB123	100	John Smith	1	20	GL Report
5	BA123	101	Jane Doe	2	30	GL Report
6	CD850	102	Peter Smith	3	50	GL Report
7
8
9	Table 2
10	ID#	Amount	Month	Settlement	Type
11	AB123	70	Jan 2022 Dep	SET010222	Credit Card
12	BA123	10	Feb 2022 Dep	SET020222	Credit Card
13	ZA123	50	Jan 2022 Dep	SET020322	Credit Card
14
15	What I Want
16	ID#	Cust ID	Cust Name	Invoice	Month	Settlement	GL Report	Credit Card	Difference
17	AB123	100	John Smith	1	Jan 2022 Dep	SET010222	70	70	0
18	BA123	101	Jane Doe	2	Feb 2022 Dep	SET020222	30	10	20
19	CD850	102	Peter Smith	3			50	0	50
Sheet1

 

[pto160]

I done more research and I created a relationship and pivot table based on the ID# per the images below, but I am not getting the correct information. The transaction ID is unique in table 2.
Do all the columns or fields need to be unique in table 2?
Is this possible with pivot tables to get the information on "What I want"?

 

[Alex Blakenburg]

I think the simplest would be to import Table1 into Power Query.
Do a group by query to total the lines into a single unique line for each combination for the total value
Import Table2 into Power Query
Do a left outer join from the Table1 query to the Table 2 query ?

 

[pto160]

Thanks. So you are saying that for example to group by query, to total the lines for example it would be

Pivot Table 2.xlsx
	A	B	C	D	E	F
1	ID#	Cust ID	Cust Name	Invoice	Amount	Type
2	AB123	100	John Smith	1	50	GL Report
3	AB123	100	John Smith	1	20	GL Report
4
5	ID#	Cust ID	Cust Name	Invoice	Amount	Type
6	AB123	100	John Smith	1	70	GL Report
Sheet3

So I won't actually be using pivot tables but Power Query for this process. Is that correct?

 

[Alex Blakenburg]

Yes, that would be my approach.

 

[pto160]

Thanks. I'll start learning about Power Query. I am surprised that pivot tables don't have this capability, as a simple vlookup from table 1 to table 2 to retrieve the month can achieve the same results.
I'm looking at speeding up my workbook as there is 80,000 formulas.