Merge Queries in Power Query

[MrTechie]

Hello community, I have data as below,

Table 1:

Date                  AccountCode                            Value

1/01/2024	null	null
31/12/2023	null	null
30/12/2023	null	null
29/12/2023	null	null
28/12/2023	null	null
27/12/2023	null	null
26/12/2023	null	null
25/12/2023	null	null
24/12/2023	null	null
23/12/2023	Allocated Delivery	0.262
23/12/2023	Authorised Overrun Reservation	0.025
22/12/2023	Authorised Overrun Reservation	0.025
22/12/2023	Allocated Delivery	0.245

Table 2:

Date               AccountCode                               Value

1/01/2024	null	null
31/12/2023	Allocated Delivery	31
31/12/2023	Authorised Overrun Reservation	31
30/12/2023	Allocated Delivery	30
30/12/2023	Authorised Overrun Reservation	30
29/12/2023	Allocated Delivery	29
29/12/2023	Authorised Overrun Reservation	29
28/12/2023	Authorised Overrun Reservation	28
28/12/2023	Allocated Delivery	28
27/12/2023	Authorised Overrun Reservation	27
27/12/2023	Allocated Delivery	27
26/12/2023	Authorised Overrun Reservation	26
26/12/2023	Allocated Delivery	26
25/12/2023	Allocated Delivery	25
25/12/2023	Authorised Overrun Reservation	25
24/12/2023	Authorised Overrun Reservation	24
24/12/2023	Allocated Delivery	24
23/12/2023	Allocated Delivery	23
23/12/2023	Authorised Overrun Reservation	23
22/12/2023	Authorised Overrun Reservation	22
22/12/2023	Allocated Delivery	22

when I merge the above two tables using Full Outer join and Date, Account Code as key columns, I am getting weird output.

 I didn't understand why the query created extra rows. My expected output is:

Finally, I want to achieve, if the date is < today, then get value else Table_2.Value.

Please suggest me how to do this. TIA

[Ahmedx · Accepted solution]

pls try this

[Ahmedx]

if you need less today then do this

[zenisekd]

@MrTechie, take a look if full outer join is really what you are looking for. https://radacad.com/choose-the-right-merge-join-type-in-power-bi

The main problem for you is that you use as the key columns 'date' and 'account code'. However, in your data you have 'account code' null in table 1 and something specific account in table 2. so instead of pairing, it creates two rows. 

Perhaps left anti would be more suitable for you...