How to reference previous row in the same column

[rs1249]

Hi Guys, I had looked at other forum on how to reference previous rows, nothing appears can solve this simple issue I had, well at least it is very simple in excel, but for some reason, in power query, I just can't get it working,

in excel the column formula suppose to work like this below, simple right

But in power query,how do I reference the previous row in the same column?

I got to here but it doesn't work:

= Table.AddColumn(#"Added Index", "Custom", each if [Entry_Type] = "Initial Entry" then [Cust_Ledger_Entry_No] else #"Added Index"[Custom]{[Index]-1})

it gives me the error:

Expression.Error: The column 'Custom' of the table wasn't found.
Details:Custom,

Anyway, here is the table and the end result in excel: the column Cust_Ledger_Entry_No_2 is the calculation column I would like to add

Entry_No	Posting_Date	Entry_Type	Document_Type	Document_No	Customer_No	Amount	Cust_Ledger_Entry_No	Cust_Ledger_Entry_No_2
1	01/10/2017	Initial Entry	Credit Memo	CRD-0000004	C0000500	-7200	5802	5802
2	01/10/2017	Application	Credit Memo	CRD-0000004	C0000500	-7200	110	5802
3	01/10/2017	Application	Credit Memo	CRD-0000004	C0000500	7200	5802	5802
4	01/10/2017	Initial Entry	Credit Memo	CRD-0000003	C0000500	-7200	5805	5805
5	01/10/2017	Application	Credit Memo	CRD-0000003	C0000500	-7200	104	5805
6	01/10/2017	Application	Credit Memo	CRD-0000003	C0000500	7200	5805	5805
7	01/10/2017	Initial Entry	Invoice	INV-0000089	C0000500	7200	5824	5824
8	24/10/2017	Initial Entry	Invoice	INV-0000090	C0000500	2880	5843	5843
9	24/10/2017	Initial Entry	Invoice	INV-0000091	C0000500	10080	5862	5862
10	01/10/2017	Initial Entry	Credit Memo	CRD-0000006	C0000490	-60000	5865	5865
11	01/10/2017	Application	Credit Memo	CRD-0000006	C0000490	-60000	118	5865
12	01/10/2017	Application	Credit Memo	CRD-0000006	C0000490	60000	5865	5865
13	01/10/2017	Initial Entry	Invoice	INV-0000092	C0000490	95999.96	5884	5884
14	01/10/2017	Initial Entry	Credit Memo	CRD-0000007	C0000420	-252	5991	5991
15	01/10/2017	Application	Credit Memo	CRD-0000007	C0000420	-252	76	5991
16	01/10/2017	Application	Credit Memo	CRD-0000007	C0000420	252	5991	5991
17	01/10/2017	Initial Entry	Credit Memo	CRD-0000008	C0000420	-350	5993	5993
18	01/10/2017	Application	Credit Memo	CRD-0000008	C0000420	-350	78	5993
19	01/10/2017	Application	Credit Memo	CRD-0000008	C0000420	350	5993	5993
20	20/11/2017	Initial Entry	Invoice	INV-0000093	C0000420	1605.6	6014	6014
21	23/11/2017	Initial Entry	Credit Memo	CRD-0000010	C0000560	-192	6019	6019
22	23/11/2017	Application	Credit Memo	CRD-0000010	C0000560	-192	5668	6019
23	23/11/2017	Application	Credit Memo	CRD-0000010	C0000560	192	6019	6019
24	01/10/2017	Initial Entry	Invoice	INV-0000094	C0000560	192	6024	6024
25	01/11/2017	Initial Entry	Invoice	INV-0000095	C0000692	24.01	6027	6027
26	03/10/2017	Initial Entry	Payment	BANK-0000034	C0000430	-114.4	6029	6029
27	03/10/2017	Application	Payment	BANK-0000034	C0000430	-114.4	84	6029
28	03/10/2017	Application	Payment	BANK-0000034	C0000430	114.4	6029	6029
29	05/10/2017	Initial Entry	Payment	BANK-0000036	C0000610	-255.2	6033	6033
30	05/10/2017	Application	Payment	BANK-0000036	C0000610	-255.2	74	6033
31	05/10/2017	Application	Payment	BANK-0000036	C0000610	255.2	6033	6033
32	09/10/2017	Initial Entry	Payment	BANK-0000038	C0000630	-35.2	6037	6037
33	11/10/2017	Initial Entry	Payment	BANK-0000040	C0000480	-1524	6041	6041
34	11/10/2017	Application	Payment	BANK-0000040	C0000480	-1524	5699	6041
35	11/10/2017	Application	Payment	BANK-0000040	C0000480	1524	6041	6041

Thanks very much for the help in advance!!!

[HotChilli · Accepted solution]

The error is thrown because the 'else' clause uses the table as it exists in the previous step, but the column 'custom' doesn't exist at that step.

I think, to keep things simple, if you just use the same code but replace the 'else' part with 'else null', you can add another step afterwards and do a 'Fill Down' that should work.

It will depend on the data being correctly ordered and there being a correct 'Initial Entry' for each 'set' of data

[rs1249]

Amazing, worked perfactly, thanks very much