Power Query to extract rows based on criteria

ronakchoudhary · Aug 31, 2020

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 · Aug 31, 2020

is that what you want?



Item Number	Item Description	GSV-Qty	GRN-Qty	MaxDate
429	a	1	1	03/07/2013
584	b	184	130	06/11/2010
0006648-801	c	60	10	26/12/2010
76	d	1	1	22/06/2014
00083-002	e	16550	3762	24/11/2014
00107-072-001	f	16	10	07/03/2020

ronakchoudhary · Aug 31, 2020

sandy666 said:
is that what you want?

Item Number Item Description GSV-Qty GRN-Qty MaxDate
429 a 1 1 03/07/2013
584 b 184 130 06/11/2010
0006648-801 c 60 10 26/12/2010
76 d 1 1 22/06/2014
00083-002 e 16550 3762 24/11/2014
00107-072-001 f 16 10 07/03/2020

Hey Sandy,

Thanks a lot for your reply. I actually need the list of invoices with respective purchase dates and not just the summary.

sandy666 · Sep 1, 2020



Item Number	Item Description	GSV QTY	GRN Ref	GRN Date	GRN Qty
429	a	1	43	03/07/2013	1
584	b	4	73	16/12/2009	3
584	b	4	22	11/12/2009	13
584	b	4	93	22/01/2010	13
584	b	4	20	02/03/2010	26
584	b	4	54	10/06/2010	13
584	b	4	23	10/06/2010	13
584	b	4	26	22/07/2010	10
584	b	4	82	02/09/2010	1
584	b	4	55	02/09/2010	1
584	b	4	30	02/09/2010	1
584	b	4	62	02/09/2010	1
584	b	4	95	02/09/2010	1
584	b	4	67	02/09/2010	1
584	b	4	71	02/09/2010	1
584	b	4	27	02/09/2010	1
584	b	4	49	02/09/2010	1
584	b	4	43	22/09/2010	1
584	b	4	17	22/09/2010	1
584	b	4	88	22/09/2010	1
584	b	4	91	22/09/2010	1
584	b	4	12	22/09/2010	1
584	b	4	24	22/09/2010	1
584	b	4	81	22/09/2010	1
584	b	4	40	22/09/2010	1
584	b	4	41	22/09/2010	1
584	b	4	25	22/09/2010	1
584	b	4	86	22/09/2010	1
584	b	4	49	22/09/2010	1
584	b	4	83	22/09/2010	1
584	b	4	27	22/09/2010	1
584	b	4	39	22/09/2010	1
584	b	4	37	22/09/2010	1
584	b	4	31	22/09/2010	1
584	b	4	71	06/11/2010	1
584	b	4	43	06/11/2010	1
584	b	4	41	06/11/2010	1
584	b	4	79	06/11/2010	1
584	b	4	44	06/11/2010	1
584	b	4	30	06/11/2010	1
584	b	4	27	06/11/2010	1
584	b	4	87	06/11/2010	1
584	b	4	88	06/11/2010	1
584	b	4	32	06/11/2010	1
584	b	4	4	06/11/2010	1
584	b	4	36	06/11/2010	1
584	b	4	13	06/11/2010	1
0006648-801	c	6	59	07/12/2006	1
0006648-801	c	6	54	05/02/2007	1
0006648-801	c	6	65	22/09/2006	1
0006648-801	c	6	88	19/08/2007	1
0006648-801	c	6	71	19/08/2007	1
0006648-801	c	6	30	11/02/2005	1
0006648-801	c	6	61	05/08/2010	1
0006648-801	c	6	38	05/08/2010	1
0006648-801	c	6	65	26/12/2010	1
0006648-801	c	6	13	26/12/2010	1
76	d	1	68	22/06/2014	1
00083-002	e	331	96	16/07/2006	30
00083-002	e	331	63	23/06/2006	30
00083-002	e	331	4	31/07/2006	30
00083-002	e	331	84	12/11/2005	58
00083-002	e	331	52	01/11/2005	62
00083-002	e	331	89	03/09/2006	26
00083-002	e	331	19	19/04/2007	50
00083-002	e	331	34	06/03/2007	40
00083-002	e	331	38	19/09/2006	174
00083-002	e	331	25	11/02/2007	34
00083-002	e	331	46	03/12/2006	34
00083-002	e	331	26	29/12/2006	116
00083-002	e	331	79	30/04/2007	26
00083-002	e	331	0	13/05/2006	10
00083-002	e	331	83	06/09/2005	20
00083-002	e	331	43	27/02/2005	30
00083-002	e	331	31	30/04/2005	30
00083-002	e	331	59	13/02/2008	21
00083-002	e	331	43	05/05/2008	40
00083-002	e	331	41	26/05/2008	27
00083-002	e	331	81	21/11/2007	52
00083-002	e	331	64	25/11/2007	140
00083-002	e	331	35	06/01/2008	87
00083-002	e	331	13	02/06/2008	700
00083-002	e	331	33	04/07/2008	28
00083-002	e	331	31	21/07/2008	172
00083-002	e	331	14	07/05/2010	50
00083-002	e	331	7	14/06/2010	75
00083-002	e	331	11	25/07/2010	100
00083-002	e	331	77	10/11/2010	60
00083-002	e	331	63	25/11/2010	40
00083-002	e	331	9	02/04/2011	21
00083-002	e	331	91	28/04/2011	79
00083-002	e	331	67	28/06/2011	49
00083-002	e	331	55	10/07/2011	51
00083-002	e	331	22	09/08/2011	52
00083-002	e	331	24	13/08/2011	48
00083-002	e	331	36	06/04/2012	150
00083-002	e	331	46	19/07/2012	100
00083-002	e	331	10	10/08/2012	100
00083-002	e	331	63	27/01/2013	200
00083-002	e	331	79	11/05/2013	26
00083-002	e	331	54	31/05/2013	100
00083-002	e	331	45	10/06/2013	34
00083-002	e	331	59	14/09/2013	36
00083-002	e	331	85	24/09/2013	44
00083-002	e	331	72	28/10/2013	80
00083-002	e	331	22	22/06/2014	17
00083-002	e	331	78	26/06/2014	83
00083-002	e	331	84	24/11/2014	100
00107-072-001	f	4	95	22/02/2014	4
00107-072-001	f	4	92	31/10/2016	2
00107-072-001	f	4	74	28/07/2019	2
00107-072-001	f	4	20	07/03/2020	2

ronakchoudhary · Sep 1, 2020

sandy666 said:
Item Number Item Description GSV QTY GRN Ref GRN Date GRN Qty
429 a 1 43 03/07/2013 1
584 b 4 73 16/12/2009 3
584 b 4 22 11/12/2009 13
584 b 4 93 22/01/2010 13
584 b 4 20 02/03/2010 26
584 b 4 54 10/06/2010 13
584 b 4 23 10/06/2010 13
584 b 4 26 22/07/2010 10
584 b 4 82 02/09/2010 1
584 b 4 55 02/09/2010 1
584 b 4 30 02/09/2010 1
584 b 4 62 02/09/2010 1
584 b 4 95 02/09/2010 1
584 b 4 67 02/09/2010 1
584 b 4 71 02/09/2010 1
584 b 4 27 02/09/2010 1
584 b 4 49 02/09/2010 1
584 b 4 43 22/09/2010 1
584 b 4 17 22/09/2010 1
584 b 4 88 22/09/2010 1
584 b 4 91 22/09/2010 1
584 b 4 12 22/09/2010 1
584 b 4 24 22/09/2010 1
584 b 4 81 22/09/2010 1
584 b 4 40 22/09/2010 1
584 b 4 41 22/09/2010 1
584 b 4 25 22/09/2010 1
584 b 4 86 22/09/2010 1
584 b 4 49 22/09/2010 1
584 b 4 83 22/09/2010 1
584 b 4 27 22/09/2010 1
584 b 4 39 22/09/2010 1
584 b 4 37 22/09/2010 1
584 b 4 31 22/09/2010 1
584 b 4 71 06/11/2010 1
584 b 4 43 06/11/2010 1
584 b 4 41 06/11/2010 1
584 b 4 79 06/11/2010 1
584 b 4 44 06/11/2010 1
584 b 4 30 06/11/2010 1
584 b 4 27 06/11/2010 1
584 b 4 87 06/11/2010 1
584 b 4 88 06/11/2010 1
584 b 4 32 06/11/2010 1
584 b 4 4 06/11/2010 1
584 b 4 36 06/11/2010 1
584 b 4 13 06/11/2010 1
0006648-801 c 6 59 07/12/2006 1
0006648-801 c 6 54 05/02/2007 1
0006648-801 c 6 65 22/09/2006 1
0006648-801 c 6 88 19/08/2007 1
0006648-801 c 6 71 19/08/2007 1
0006648-801 c 6 30 11/02/2005 1
0006648-801 c 6 61 05/08/2010 1
0006648-801 c 6 38 05/08/2010 1
0006648-801 c 6 65 26/12/2010 1
0006648-801 c 6 13 26/12/2010 1
76 d 1 68 22/06/2014 1
00083-002 e 331 96 16/07/2006 30
00083-002 e 331 63 23/06/2006 30
00083-002 e 331 4 31/07/2006 30
00083-002 e 331 84 12/11/2005 58
00083-002 e 331 52 01/11/2005 62
00083-002 e 331 89 03/09/2006 26
00083-002 e 331 19 19/04/2007 50
00083-002 e 331 34 06/03/2007 40
00083-002 e 331 38 19/09/2006 174
00083-002 e 331 25 11/02/2007 34
00083-002 e 331 46 03/12/2006 34
00083-002 e 331 26 29/12/2006 116
00083-002 e 331 79 30/04/2007 26
00083-002 e 331 0 13/05/2006 10
00083-002 e 331 83 06/09/2005 20
00083-002 e 331 43 27/02/2005 30
00083-002 e 331 31 30/04/2005 30
00083-002 e 331 59 13/02/2008 21
00083-002 e 331 43 05/05/2008 40
00083-002 e 331 41 26/05/2008 27
00083-002 e 331 81 21/11/2007 52
00083-002 e 331 64 25/11/2007 140
00083-002 e 331 35 06/01/2008 87
00083-002 e 331 13 02/06/2008 700
00083-002 e 331 33 04/07/2008 28
00083-002 e 331 31 21/07/2008 172
00083-002 e 331 14 07/05/2010 50
00083-002 e 331 7 14/06/2010 75
00083-002 e 331 11 25/07/2010 100
00083-002 e 331 77 10/11/2010 60
00083-002 e 331 63 25/11/2010 40
00083-002 e 331 9 02/04/2011 21
00083-002 e 331 91 28/04/2011 79
00083-002 e 331 67 28/06/2011 49
00083-002 e 331 55 10/07/2011 51
00083-002 e 331 22 09/08/2011 52
00083-002 e 331 24 13/08/2011 48
00083-002 e 331 36 06/04/2012 150
00083-002 e 331 46 19/07/2012 100
00083-002 e 331 10 10/08/2012 100
00083-002 e 331 63 27/01/2013 200
00083-002 e 331 79 11/05/2013 26
00083-002 e 331 54 31/05/2013 100
00083-002 e 331 45 10/06/2013 34
00083-002 e 331 59 14/09/2013 36
00083-002 e 331 85 24/09/2013 44
00083-002 e 331 72 28/10/2013 80
00083-002 e 331 22 22/06/2014 17
00083-002 e 331 78 26/06/2014 83
00083-002 e 331 84 24/11/2014 100
00107-072-001 f 4 95 22/02/2014 4
00107-072-001 f 4 92 31/10/2016 2
00107-072-001 f 4 74 28/07/2019 2
00107-072-001 f 4 20 07/03/2020 2

Hey Sandy,
Thanks a lot for quick guidance.

I need another help, if possible:

I am applying a custom step for conditional running total as below but it is significantly slowing down my refresh status. I was hoping if you could help me clean this up (buffer command or something) to make it fast. My total data is about 200,000 rows and I have to refresh it every week. I cannot be waiting for 15 mins everytime to refresh it. Could you help on this as well?

let

Source = Table.Buffer(Merged), This step picks up data from another table - if I link the same table, it gives me circular reference error.

AddIndex = Table.AddIndexColumn(Source, "Row", 1, 1), This is adding an index which will be used in the next column.

RunTotal = Table.AddColumn(AddIndex, "Running Total", each let Item=[Item], Row=[Row] in List.Sum(Table.SelectRows(AddIndex, each [Row]<=Row and [Item]=Item)[GRN Qty])), Here is where the actual work happens, basically, if the 'item' is same, it should add the 'GRN Qty' with previous record otherwise start afresh.

RemoveIndex = Table.RemoveColumns(RunTotal,{"Row"})

in

RemoveIndex

Note: I know there is an alternative to use a function for adding running sum, however, my users are novices in power query and I don't want to complicate things for them. If this is the only solution, please let me know.

Appreciate your help and support.

sandy666 · Sep 1, 2020

post whole code in code tags without comments
plus
code for merge

code should work with your examples

btw. your example is not representative

ronakchoudhary said:
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

ronakchoudhary · Sep 1, 2020

sandy666 said:
post whole code in code tags without comments
plus
code for merge

code should work with your examples

btw. your example is not representative

Hi Sandy,

Here you go:

Code:

Power Query:

let

Source = Table.Buffer(Merged),

AddIndex = Table.AddIndexColumn(Source, "Row", 1, 1),

RunTotal = Table.AddColumn(AddIndex, "Running Total", each let Item=[Item], Row=[Row] in List.Sum(Table.SelectRows(AddIndex, each [Row]<=Row and [Item]=Item)[GRN Qty])),

RemoveIndex = Table.RemoveColumns(RunTotal,{"Row"})

in

RemoveIndex

Note that the code is working, its just too slow.

And yes, I noticed that the sample I shared actually did not cover the real problem I am facing. Will try to share something.

sandy666 · Sep 1, 2020

where is the code for "Merged"?

ronakchoudhary · Sep 1, 2020

sandy666 said:
where is the code for "Merged"?

Sorry, missed that. Here you go:

Power Query:

let
    Source = Table.NestedJoin(GSV_R, {"Item"}, GRN_R, {"Item Number"}, "GRN_R", JoinKind.LeftOuter),
    #"Expanded GRN_R" = Table.ExpandTableColumn(Source, "GRN_R", {"Alpha", "GRN Ref", "GRN Date", "GRN Qty"}, {"Alpha", "GRN Ref", "GRN Date", "GRN Qty"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded GRN_R",{"Total"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Item", Order.Ascending}, {"GRN Date", Order.Descending}}),
    #"Merged Queries" = Table.NestedJoin(#"Sorted Rows", {"Item"}, GSV_R_Pivot, {"Item"}, "GSV_R_Pivot", JoinKind.LeftOuter),
    #"Expanded GSV_R_Pivot" = Table.ExpandTableColumn(#"Merged Queries", "GSV_R_Pivot", {"Comments"}, {"Comments"})
in
    #"Expanded GSV_R_Pivot"

sandy666 · Sep 1, 2020

Your code is not compatible with your example

Power Query to extract rows based on criteria

ronakchoudhary

New Member

Excel Facts

sandy666

Banned - Rules violations

ronakchoudhary

New Member

sandy666

Banned - Rules violations

ronakchoudhary

New Member

sandy666

Banned - Rules violations

ronakchoudhary

New Member

sandy666

Banned - Rules violations

ronakchoudhary

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

Power Query to extract rows based on criteria

New Member

Excel Facts

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

New Member

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock