Conditional column to pick up cell value above a particular cell

[Apprentice1000]

Hi, would appreciate help on this. I'm new to power query and trying to add a conditional column.

I need to grab the number above "Opening Balance" in column A (Our Ref)

This is the GL Code and it's too time consuming to fill this in manually. Thanks

Our Ref	Acc No	Your Ref	Pr Yr	Date	Due Date	Description	Debit	Credit
100, Interest Earned
Opening Balance							0	0
BNK0001		Bank interest recvd	Apr/2021	30/04/2021		Bank interest		1.5
BNK0002		Bank interest recvd	Apr/2021	30/04/2021		Bank interest		0.2
						Totals.:		1.7
Closing Balance							0	1.7
110, Sales
Opening Balance							0	0
SI000001	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 123		200
SI000002	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 124		100
SI000003	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 125		300
SI000004	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 126		400
SI000005	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 127		500
SI000006	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 128		600
SI000007	CUST1	JOB 00001	Apr/2021	1/04/2021	1/05/2021	Product description 129		700
SI000008	CUST2	BLAHBLAH	Apr/2021	1/04/2021	20/05/2021	Product description 130
SI000009	CUST3	SDFKJSDF	Apr/2021	1/04/2021	1/05/2021	Product description 131		200
SI000010	CUST4	JOB NO1000	Apr/2021	1/04/2021	20/05/2021	Product description 132		200
SI000011	CUST4	JOB NO1000	Apr/2021	1/04/2021	20/05/2021	Product description 133		200
SI000012	CUST5	51510200	Apr/2021	1/04/2021	20/05/2021	Product description 134		200
SI000013	CUST5	51510200	Apr/2021	1/04/2021	20/05/2021	Product description 135		200
SI000014	CUST5	51510200	Apr/2021	1/04/2021	20/05/2021	Product description 136		200
						Totals.:		4000
Closing Balance								4000
120, Purchases
Opening Balance							0	0
PI00001	VEND1	I0000100	Apr/2021	1/04/2021	20/05/2021	Supplier invoice 1	500
PI00002	VEND1	I0000101	Apr/2021	1/04/2021	20/05/2021	Supplier invoice 2	2000
PI00003	VEND1	I0000102	Apr/2021	1/04/2021	20/05/2021	Supplier invoice 3	1000
						Totals.:	3500
Closing Balance							3500	0

[Jakinta · Accepted solution]

You can try with this in New blank query and adjust steps to your source table.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdVPb4IwFADwr9JwNvraArrdRF2mLmqC28V4INhsZAQIoJ9/VF8X6h9GZj1UsGn7o6+v7WZjUYAOmSalyEVRkkmQJ2JndSzSWLadjbXMRBIln8QL4iAJRUN7OBbZx1vMAYBivRck3yRSdC7Cg5SHWd5jwGQjDj2w1Z+LHlhHu05tbGZ0bOiy49i3ZrZOyyAuus+/n9I/Nh/FadE+NKpXc8hV2Cmt1ssPYlE8bJX8KQAu0+jdX8vnbOkRVVcLIq3FsHp31PsqT3f7sCQ7UYR5lJVRmhDKOEoMNIeZdmy1HLrDTTsOOlx3bNOOi46tO45pp4+OozuuaWeAjqs7fdPOEzp93RmgI/POexu+ynKbYfCXw0Hbcag8oSKzzh+/zGfVzx2T4eqY0jcPBWRsDNpiWWU93DUddl2i5qXrBwJVB4JMcIc6FNidjn3d4aYd57pjm3bcM6fl9WQDtm9/P2ndWl5QrLqgVvs8/AqKB15Sq6k6Hz4mi7F84oZoG1p/n2VxJPLqzj+kUSgIxaNPfdVJYJdCw6HULLDTmp0T/JJg/yXkWLROtMgNXp91+9zAbtVybH8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Our Ref" = _t, #"Acc No" = _t, #"Your Ref" = _t, #"Pr Yr" = _t, Date = _t, #"Due Date" = _t, Description = _t, Debit = _t, Credit = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Number", each if Text.StartsWith(#"Added Index"[Our Ref]{[Index]+1},"Opening") then Text.BeforeDelimiter([Our Ref], ",") else null, type number )
in
    #"Added Custom"

...or...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tdVPb4IwFADwr9JwNvraArrdRF2mLmqC28V4INhsZAQIoJ9/VF8X6h9GZj1UsGn7o6+v7WZjUYAOmSalyEVRkkmQJ2JndSzSWLadjbXMRBIln8QL4iAJRUN7OBbZx1vMAYBivRck3yRSdC7Cg5SHWd5jwGQjDj2w1Z+LHlhHu05tbGZ0bOiy49i3ZrZOyyAuus+/n9I/Nh/FadE+NKpXc8hV2Cmt1ssPYlE8bJX8KQAu0+jdX8vnbOkRVVcLIq3FsHp31PsqT3f7sCQ7UYR5lJVRmhDKOEoMNIeZdmy1HLrDTTsOOlx3bNOOi46tO45pp4+OozuuaWeAjqs7fdPOEzp93RmgI/POexu+ynKbYfCXw0Hbcag8oSKzzh+/zGfVzx2T4eqY0jcPBWRsDNpiWWU93DUddl2i5qXrBwJVB4JMcIc6FNidjn3d4aYd57pjm3bcM6fl9WQDtm9/P2ndWl5QrLqgVvs8/AqKB15Sq6k6Hz4mi7F84oZoG1p/n2VxJPLqzj+kUSgIxaNPfdVJYJdCw6HULLDTmp0T/JJg/yXkWLROtMgNXp91+9zAbtVybH8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Our Ref" = _t, #"Acc No" = _t, #"Your Ref" = _t, #"Pr Yr" = _t, Date = _t, #"Due Date" = _t, Description = _t, Debit = _t, Credit = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Number", each try Number.From(Text.BeforeDelimiter([Our Ref], ",")) otherwise null, type number)
in
    #"Added Custom"