Power Query to extract rows based on criteria

[ronakchoudhary]

Hi there,

I have two data sets - one has total quantity and one has all invoice details (Invoice number, Quantity, Date). I need to extract all invoices which comprise of the quantity on hand. Data set as below:

Table 1 - Quantity on Hand:

Item Number	Item Description	GSV QTY
00000429	a	1​
00000584	b	4​
0006648-801	c	6​
00076	d	1​
00083-002	e	331​
00107-072-001	f	4​

Table 2 - Invoice Data:

Item Number	Item Description	GRN Ref	GRN Date	GRN Qty
00000429	a	43	03-Jul-13​	1​
00000584	b	73	16-Dec-09​	3​
00000584	b	22	11-Dec-09​	13​
00000584	b	93	22-Jan-10​	13​
00000584	b	20	02-Mar-10​	26​
00000584	b	54	10-Jun-10​	13​
00000584	b	23	10-Jun-10​	13​
00000584	b	26	22-Jul-10​	10​
00000584	b	82	02-Sep-10​	1​
00000584	b	55	02-Sep-10​	1​
00000584	b	30	02-Sep-10​	1​
00000584	b	62	02-Sep-10​	1​
00000584	b	95	02-Sep-10​	1​
00000584	b	30	02-Sep-10​	1​
00000584	b	67	02-Sep-10​	1​
00000584	b	71	02-Sep-10​	1​
00000584	b	27	02-Sep-10​	1​
00000584	b	49	02-Sep-10​	1​
00000584	b	43	22-Sep-10​	1​
00000584	b	17	22-Sep-10​	1​
00000584	b	88	22-Sep-10​	1​
00000584	b	91	22-Sep-10​	1​
00000584	b	12	22-Sep-10​	1​
00000584	b	24	22-Sep-10​	1​
00000584	b	81	22-Sep-10​	1​
00000584	b	40	22-Sep-10​	1​
00000584	b	41	22-Sep-10​	1​
00000584	b	25	22-Sep-10​	1​
00000584	b	25	22-Sep-10​	1​
00000584	b	86	22-Sep-10​	1​
00000584	b	49	22-Sep-10​	1​
00000584	b	83	22-Sep-10​	1​
00000584	b	27	22-Sep-10​	1​
00000584	b	43	22-Sep-10​	1​
00000584	b	41	22-Sep-10​	1​
00000584	b	39	22-Sep-10​	1​
00000584	b	37	22-Sep-10​	1​
00000584	b	31	22-Sep-10​	1​
00000584	b	71	06-Nov-10​	1​
00000584	b	43	06-Nov-10​	1​
00000584	b	41	06-Nov-10​	1​
00000584	b	79	06-Nov-10​	1​
00000584	b	44	06-Nov-10​	1​
00000584	b	30	06-Nov-10​	1​
00000584	b	27	06-Nov-10​	1​
00000584	b	87	06-Nov-10​	1​
00000584	b	88	06-Nov-10​	1​
00000584	b	32	06-Nov-10​	1​
00000584	b	4	06-Nov-10​	1​
00000584	b	36	06-Nov-10​	1​
00000584	b	13	06-Nov-10​	1​
0006648-801	c	59	07-Dec-06​	1​
0006648-801	c	54	05-Feb-07​	1​
0006648-801	c	65	22-Sep-06​	1​
0006648-801	c	88	19-Aug-07​	1​
0006648-801	c	71	19-Aug-07​	1​
0006648-801	c	30	11-Feb-05​	1​
0006648-801	c	61	05-Aug-10​	1​
0006648-801	c	38	05-Aug-10​	1​
0006648-801	c	65	26-Dec-10​	1​
0006648-801	c	13	26-Dec-10​	1​
00076	d	68	22-Jun-14​	1​
00083-002	e	96	16-Jul-06​	30​
00083-002	e	63	23-Jun-06​	30​
00083-002	e	4	31-Jul-06​	30​
00083-002	e	84	12-Nov-05​	58​
00083-002	e	52	01-Nov-05​	62​
00083-002	e	89	03-Sep-06​	26​
00083-002	e	19	19-Apr-07​	50​
00083-002	e	34	06-Mar-07​	40​
00083-002	e	38	19-Sep-06​	174​
00083-002	e	25	11-Feb-07​	34​
00083-002	e	46	03-Dec-06​	34​
00083-002	e	26	29-Dec-06​	116​
00083-002	e	79	30-Apr-07​	26​
00083-002	e	0	13-May-06​	10​
00083-002	e	83	06-Sep-05​	20​
00083-002	e	43	27-Feb-05​	30​
00083-002	e	31	30-Apr-05​	30​
00083-002	e	59	13-Feb-08​	21​
00083-002	e	43	05-May-08​	40​
00083-002	e	41	26-May-08​	27​
00083-002	e	81	21-Nov-07​	52​
00083-002	e	64	25-Nov-07​	140​
00083-002	e	35	06-Jan-08​	87​
00083-002	e	13	02-Jun-08​	700​
00083-002	e	33	04-Jul-08​	28​
00083-002	e	31	21-Jul-08​	172​
00083-002	e	14	07-May-10​	50​
00083-002	e	7	14-Jun-10​	75​
00083-002	e	11	25-Jul-10​	100​
00083-002	e	77	10-Nov-10​	60​
00083-002	e	63	25-Nov-10​	40​
00083-002	e	9	02-Apr-11​	21​
00083-002	e	91	28-Apr-11​	79​
00083-002	e	67	28-Jun-11​	49​
00083-002	e	55	10-Jul-11​	51​
00083-002	e	22	09-Aug-11​	52​
00083-002	e	24	13-Aug-11​	48​
00083-002	e	36	06-Apr-12​	150​
00083-002	e	46	19-Jul-12​	100​
00083-002	e	10	10-Aug-12​	100​
00083-002	e	63	27-Jan-13​	200​
00083-002	e	79	11-May-13​	26​
00083-002	e	54	31-May-13​	100​
00083-002	e	45	10-Jun-13​	34​
00083-002	e	59	14-Sep-13​	36​
00083-002	e	85	24-Sep-13​	44​
00083-002	e	72	28-Oct-13​	80​
00083-002	e	22	22-Jun-14​	17​
00083-002	e	78	26-Jun-14​	83​
00083-002	e	84	24-Nov-14​	100​
00107-072-001	f	95	22-Feb-14​	4​
00107-072-001	f	92	31-Oct-16​	2​
00107-072-001	f	74	28-Jul-19​	2​
00107-072-001	f	20	07-Mar-20​	2​

Note that total invoice quantity can be higher than, equal to or lower than quantity on hand.

If total quantity in various invoices is equal to quantity on hand, data should pick up all invoices.
If total quantity in various invoices is lower than quantity on hand, data should pick up all invoices.
If total quantity in various invoices is higher than quantity on hand, data should pick up latest invoices (based on date) which make up the quantity on hand.

Can someone help me with how to do this in M Query?

Thanks in advance!!

 

[sandy666]

maybe post a link to the shared excel file with your data and code via googledrive, onedrive, dropbox or any similar service

 

[ronakchoudhary]

S

Your code is not compatible with your example

Sorry but what do I need to do to make it compatible?

 

[sandy666]

see post#11 (preferred) - with sample it will to be hard to say it works slow or fast
or
post example with original structure without any changes (which your code works with)

 

[sandy666]

but before you post example or excel file try
1. add primary key
2. remove duplicates after merge
3. use Table.Buffer (you'll need to find where it will work the best)
4. read this

 

[ronakchoudhary]

but before you post example or excel file try
1. add primary key
2. remove duplicates after merge
3. use Table.Buffer (you'll need to find where it will work the best)
4. read this

Hey Sandy, thanks for your feedback. Adding Table.Buffer and primary key really helped. It now takes 3 mins rather than 15 mins. I am guessing 3 mins for a data with over 200,000 rows is justified so living with this. Thanks again for your help. Cheers.

 

[sandy666]

You are welcome