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!

 

[jasonb75]

In your 'Results that I need' table you show Biscuits paired with Target and Bread paired with WalMart. Could you explain why it is that way around and not Bread paired with Target and Biscuits paired with WalMart?

There is nothing in either table to clearly identify which rows should be paired in that way, which is why your attempt has split the results over 2 rows each time.

 

[isafu]

In your 'Results that I need' table you show Biscuits paired with Target and Bread paired with WalMart. Could you explain why it is that way around and not Bread paired with Target and Biscuits paired with WalMart?

There is nothing in either table to clearly identify which rows should be paired in that way, which is why your attempt has split the results over 2 rows each time.

Hi, thank you for responding!

The location of purchase is not important, I just need it for a label for this report.

August 2nd has BREAD and COOKIE
But only 1 purchase of flour at TARGET

The flour purchase could be merged to either of the August 2nd entry, second entry's purchase and location column would be simply blank

The opposite is true, as seen in August 3rd

Please let me know if the description is unclear, as English is not my first language

Originally the report was typed manually, I am trying to automate the process, but I was not allowed to change the report format ("Result that I need" table)

Do you have any suggestion to simplify the process?

 

[jasonb75]

I certainly don't see any way of doing it in power query, for that you would need a unique common key to match both tables.

I'll set up a test sheet and try a few things but I suspect that you will have to rethink your method of getting your final result.

 

[isafu]

I certainly don't see any way of doing it in power query, for that you would need a unique common key to match both tables.

I'll set up a test sheet and try a few things but I suspect that you will have to rethink your method of getting your final result.

I have been trying to use a help column of some sort, is it possible to assign a number to the first occurring date on both the PURCHASE and PRODUCTION table?
So it can be the unique common key to merge, as the order is not important, only the date matters

 

[jasonb75]

I was trying something similar after my last reply but had no success, I don't think that PQ is capable of merging the data in the way that you want to.

I was going to ask another member that I know is extremely good at doing impossible things with PQ to have a look at your question, but they have unfortunately been banned from this forum and don't appear to be active on any of the other forums where I have seen them posting previously.

Does it specifically have to be PQ, or are you open to alternative methods (either vba, or formulas if the real tables are not too large)?

 

[isafu]

I was trying something similar after my last reply but had no success, I don't think that PQ is capable of merging the data in the way that you want to.

I was going to ask another member that I know is extremely good at doing impossible things with PQ to have a look at your question, but they have unfortunately been banned from this forum and don't appear to be active on any of the other forums where I have seen them posting previously.

Does it specifically have to be PQ, or are you open to alternative methods (either vba, or formulas if the real tables are not too large)?

Thank you for going so far to help

I am open to trying other solution!

PQ is just the first thing to mind because I have been using it to work with most reports.

The real tables wouldn't be too large, as it will be a monthly report, already filtered into each category without messing with my main database

 

[jasonb75]

I'm not giving up on PQ just yet, it's getting closer but not quite there.

PQ test.xlsx
	A	B	C	D	E	F	G	H
1	Code	Date	Product	Qty Used	Table2.Code	Table2.Date	Table2.Purchase Qty	Table2.Location
2	1	01/08/2022	Bread	30	1	01/08/2022	100	WalMart
3	1	01/08/2022	Biscuits	10	1	01/08/2022	50	Target
4	1	02/08/2022	Cookies	15	1	02/08/2022	50	Target
5	1	03/08/2022	Bread	30	1	03/08/2022	20	WalMart
6					1	03/08/2022	20	Target
7	1	02/08/2022	Brownies	10
Merge1

 

[jasonb75]

I'm still getting no closer to resolving this in PQ (don't like being defeated but it's getting close). I've asked another member who has better knowledge of PQ if they would be kind enough to take a look at this for you.

Mini sheet version of Tables A and B for testing purposes.

PQ test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Key	Code	Date	Purchase Qty	Location		Key	Code	Date	Product	Qty Used
2	1-44774-1	1	01/08/2022	100	WalMart		1-44774-1	1	01/08/2022	Bread	30
3	1-44774-2	1	01/08/2022	50	Target		1-44774-2	1	01/08/2022	Biscuits	10
4	1-44775-1	1	02/08/2022	50	Target		1-44775-1	1	02/08/2022	Cookies	15
5	1-44776-1	1	03/08/2022	20	WalMart		1-44775-2	1	02/08/2022	Brownies	10
6	1-44776-2	1	03/08/2022	20	Target		1-44776-1	1	03/08/2022	Bread	30
Sheet10

Cell Formulas
Range	Formula
A2:A6,G2:G6	A2	=[@Code]&"-"&[@Date]&"-"&COUNTIF(INDEX([Date],1):[@Date],[@Date])

 

[JGordon11]

let
    Purch = Excel.CurrentWorkbook(){[Name="Purchase"]}[Content],
    Prod = Excel.CurrentWorkbook(){[Name="Product"]}[Content],
    tbl1 = Prod & Purch,
    Group = Table.Group(tbl1, {"Date"}, {{"All", each _}}),
    tbl2 = Table.AddColumn(Group, "Custom", each let lst = List.RemoveNulls([All][Purchase Qty]) in lst & List.Repeat({null}, Table.RowCount([All]) - List.Count(lst))),
    tbl3 = Table.AddColumn(tbl2, "Custom1", each let lst = List.RemoveNulls([All][Location]) in lst & List.Repeat({null}, Table.RowCount([All]) - List.Count(lst))),
    tbl4 = Table.AddColumn(tbl3, "Composite", each 
            Table.SelectRows(Table.FromColumns(List.FirstN(Table.ToColumns([All]), 4) & {[Custom]} &{[Custom1]}, Table.ColumnNames([All])), each List.Count(List.RemoveNulls(Record.ToList(_))) <>2)),
    tbl5 = Table.RemoveColumns(tbl4,{"All", "Custom", "Custom1"}),
    tbl6 = Table.ExpandTableColumn(tbl5, "Composite", {"Code", "Product", "Qty Used", "Purchase Qty", "Location"} ),
    tbl7 = Table.TransformColumnTypes(tbl6,{{"Date", type date}}),
    Result = Table.ReorderColumns(tbl7,{"Code", "Date", "Product", "Qty Used", "Purchase Qty", "Location"})
in
    Result

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Code	Date	Purchase Qty	Location		Code	Date	Product	Qty Used		Code	Date	Product	Qty Used	Purchase Qty	Location
2	1	8/1/2022	100	WalMart		1	8/1/2022	Bread	30		1	8/1/2022	Bread	30	100	WalMart
3	1	8/1/2022	50	Target		1	8/1/2022	Biscuits	10		1	8/1/2022	Biscuits	10	50	Target
4	1	8/2/2022	50	Target		1	8/2/2022	Cookies	15		1	8/2/2022	Cookies	15	50	Target
5	1	8/3/2022	20	WalMart		1	8/2/2022	Brownies	10		1	8/2/2022	Brownies	10
6	1	8/3/2022	20	Target		1	8/3/2022	Bread	30		1	8/3/2022	Bread	30	20	WalMart
7											1	8/3/2022			20	Target
8
Sheet6