Power Query- unique merge situation

[jrg24]

full disclosure, I am using power query in Excel. I also don't know if what I want to do is possible or practical.

We have a client that forces us to use a web portal to invoice them. Unfortunately for us, this web portal is terrible. we have to select their reference numbers out of the portal and for most of our loads (we are in trucking), we will need two reference numbers. The idea is supposed to be specific identification , but it doesn't really work that way and instead we select reference numbers based upon dates (our loads show completion on date X so we attempt to grab two reference numbers from date X. If not enough are available we will grab others around that date. long story short, we regularly get wrapped around the axle and billing is very difficult. I am trying to sort out and arrange everything up front before we begin billing. I have built a bot to do the actual billing for us, but I still have to get everything outlined first.

My question: I have a table with a list of transactions on it. Each transaction has a date. Each transaction will require two reference numbers. On another table I have a list of reference numbers that also have a date. The count of reference numbers required and count of reference numbers available is pretty much always different. I would like to be able to merge the available reference numbers to available spots without duplicating any numbers. Once this is done I can do an anti join to then get a list of unassigned reference numbers that I can arbitrarily assign as required.

Has anyone done any thing like this before? If so, how would you go about it if you thin kit was possible?

 

[sandy666]

I would like to be able to merge the available reference numbers to available spots without duplicating any numbers. Once this is done I can do an anti join to then get a list of unassigned reference numbers that I can arbitrarily assign as required.

did you try this?
it's work for you?
what is the main question?
are you able to post an example (with generic data but with original structure) of your source data and expected result?

too little practical information and too much theory

 

[jrg24]

The below images encapsulate what I'm trying to do. I did not include all data as it is far too much to practically put here and to be honest none of the other fields are pertinent to the problem. I hope this communicates the issue well enough. The Other info image should help to interpret the desired results image.

Edit: the merge is not a normal merge. I don't know what the proper terminology would be but its kind of a fuzzy logic version where I want you to fill up the potential spots on the transaction with references numbers to the extent they are available by date. Its not a typical 1 to 1 merge like would normally be used. The only real key we have is the dates on both tables and that ends up being a many to many relationship.

 

[sandy666]

sorry mate but pictures are useless, cannot be used to edit or something.
use xl2bb to post copyable data

 

[jrg24]

example.xlsx
	A	B
1	Table1- Transactions
2	TransId	Date
3	123456	8/4/2020
4	123457	8/4/2020
5	123458	8/4/2020
6	123459	8/4/2020
7	123460	8/4/2020
8	123461	8/4/2020
9	123462	8/4/2020
10	123463	8/3/2020
11	123464	8/3/2020
12	123465	8/3/2020
13	123466	8/3/2020
14	123467	8/2/2020
15	123468	8/2/2020
16	123469	8/2/2020
17	123470	8/2/2020
18	123471	8/2/2020
19	123472	8/2/2020
20
Table1- Transactions

example.xlsx
	A	B
1	Table2- Reference numbers
2	Reference numbers	Date
3	940810	8/4/2020
4	344486	8/4/2020
5	127890	8/4/2020
6	400673	8/4/2020
7	928516	8/4/2020
8	495286	8/4/2020
9	982866	8/4/2020
10	665931	8/4/2020
11	569147	8/4/2020
12	627603	8/4/2020
13	492154	8/4/2020
14	196459	8/4/2020
15	496682	8/4/2020
16	224844	8/3/2020
17	284376	8/3/2020
18	662289	8/3/2020
19	553266	8/3/2020
20	549280	8/3/2020
21	716179	8/3/2020
22	268059	8/3/2020
23	628880	8/3/2020
24	774930	8/3/2020
25	133282	8/2/2020
26	880719	8/2/2020
27	565418	8/2/2020
28	158259	8/2/2020
29	546765	8/2/2020
30	320134	8/2/2020
31	106937	8/2/2020
32	451997	8/2/2020
33	864563	8/2/2020
Table2- Reference numbers

example.xlsx
	A	B	C	D
1	Desired output
2	From Table 1		From Table 2
3	TransId	Date	Ref 1	Ref 2
4	123456	8/4/2020	940810	665931
5	123457	8/4/2020	344486	569147
6	123458	8/4/2020	127890	627603
7	123459	8/4/2020	400673	492154
8	123460	8/4/2020	928516	196459
9	123461	8/4/2020	495286	496682
10	123462	8/4/2020	982866
11	123463	8/3/2020	224844	549280
12	123464	8/3/2020	284376	716179
13	123465	8/3/2020	662289	268059
14	123466	8/3/2020	553266	628880
15	123467	8/2/2020	133282	106937
16	123468	8/2/2020	880719	451997
17	123469	8/2/2020	565418	864563
18	123470	8/2/2020	158259
19	123471	8/2/2020	546765
20	123472	8/2/2020	320134
Desired output

example.xlsx
	G	H	I	J	K	L	M
24	Other Info
25	This is only for ease of explanation and is not a part of the desired output. I don’t care of the output is pivoted like it is here, I just did that for ease of demonstration
26
27	Date	Transaction Count	Reference numbers needed	Reference numbers available	Reference numbers used	Over/Short	Notes
28	8/4/2020	7	14	13	13	-1	Needed 14, only 13 available, one spot left null
29	8/3/2020	4	8	9	8	1	one more reference provided than number than needed, only 8 added to final output
30	8/2/2020	6	12	9	9	-3	Needed 12, only 9 provided, three spots left null
31	Total	17	34	31	30	-3	All in, we were able to use 30 of the 31 provided reference numbers
Desired output

 

[sandy666]

ok, i'll look at this

 

[jrg24]

I found what I need. I found a way to index by group that should allow what I need to do. I will index each item by date, which ill then give me a unique key to do my merge. Thanks for your help

 

[sandy666]

Glad you solved it
Have a nice day