Merging rows based on dates (multiple rows have same date but different data)

[isafu]

Hi, I'm very new at using Power Query, I would appreciate any pointers / guide to solve this case

Data source are two different table, purchase and production

I need to make a report table based on date, as shown on the bottom right table


What I have tried:

Using unpivot

Result:

Expression.Error: There weren't enough elements in the enumeration to complete the operation

Thank you!

 

[KRice]

Well I finally got there too. It's a good thing I didn't refresh my web browser sooner to see the fantastic solution from @JGordon11...I would have given up. Well done! Mine is much less efficient and much longer. I'll need to spend some time dissecting the code to see how this was done. I invested a lot of effort into developing a scheme for determining how many items on each date can be paired and which ones will exist only as singletons. And to keep all of the intermediate results organized, I split the query into multiple queries, so it's long and more convoluted, but it is available here should anyone wish to review/try it.

MrExcel_20220915_isafu.xlsx

Shared with Dropbox

www.dropbox.com

My pairings happen to be the same as those shown by @JGordon11.

MrExcel_20220915_isafu.xlsx
	A	B	C	D	E	F	G	H	I
1	Table A					Table B
2	Purchased (P)					Used (U)
3
4	Code	Date	Purchase Qty	Location		Code	Date	Product	Qty Used
5	1	8/1/2022	100	WalMart		1	8/1/2022	Bread	30
6	1	8/1/2022	50	Target		1	8/1/2022	Biscuits	10
7	1	8/2/2022	50	Target		1	8/2/2022	Cookies	15
8	1	8/3/2022	20	WalMart		1	8/2/2022	Brownies	10
9	1	8/3/2022	20	Target		1	8/3/2022	Bread	30
10
11
12	Results
13
14	Code	Date	Product	Qty Used	Purchase Qty	Location
15	1	8/1/2022	Biscuits	10	50	Target
16	1	8/1/2022	Bread	30	100	WalMart
17	1	8/2/2022	Brownies	10
18	1	8/2/2022	Cookies	15	50	Target
19	1	8/3/2022			20	Target
20	1	8/3/2022	Bread	30	20	WalMart
Sheet1

 

[jasonb75]

Thanks for taking a look at this, Kirk

I was trying multiple queries but got nowhere with it, I've done similar by aggregating numeric data but couldn't see that it would be possible with text fields.
I think I need to study @JGordon11's method as well, looking at the amount of code used, I think that I was trying to seriously over complicate the task.

 

[KRice]

I was happy to have a look and give it a go.