Forum Discussion
Solved
Conditional column to pick up cell value above a particular cell
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 |
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"
1 Reply
JakintaSolution Sage
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"