Multiple statement lines per transaction

[Nygie]

Good morning all.

Recently I have been tasked with the rarity of a physical bank statement. the easy answer to my question is to get the statement in CSV but this is not always possible.

The statement in question when scanned from PDF generates up to 6 lines for the transaction description example below. I was wondering if there is a way to make it one line per transaction combining the multiple description cells and deleting the blank lines left behind.
I almost have it in my head that the date cell needs to be read and count empty cells below, if empty cells are detected cut the data for the last 4 cells and add to the line above until the line is a single line, delete the lines just made empty and repeat.
Sounds simple when I say that but my coding skills are lacklustre at best. After doing some Googling Concatenate function appeared frequently.
Any idea/pointers in the right direction would be appreciated.
Many thanks

Date	Details	Description	Withdrawn	Paid In	Balance
4/1/2021​	Online Transaction	Station
		Pub
		VIA MOBILE - PYMT		1,000.00​	1,525.10​
4/1/2021​	Charges	11DEC A/C xxxxxx	8.00​		1,517.10​
05/01/2021​	OnLine Transaction	John Smith
		WAGE
		VIA ONLINE - PYMTB		715.00​	2,232.10​

 

[JGordon11]

with Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    lst = List.Zip({List.Skip(Source[Date]), {1.. List.Count(Source[Date])-1}}),
    lst1 = List.Accumulate(lst, {1}, (s,c)=> s & (if c{0} = null then {List.Last(s)} else {List.Last(s) +1 }  )),
    tcn = Table.ColumnNames(Source),
    tbl = Table.FromColumns({lst1} & Table.ToColumns(Source), {"ID"} & tcn),
    Group = Table.Group(tbl, {"ID"}, {{"All", each 
        let 
            Fill = Table.FillUp(_,{"Withdrawn", "Paid In", "Balance"}),
            tbl1 = Table.FirstN(Fill,1),
            tbl2 = Table.TransformColumns(tbl1, {"Description", (x)=> Text.Combine(Fill[Description], "; ")})
        in 
            tbl2}}),
    RemoveColumn = Table.RemoveColumns(Group,{"ID"}),
    Expand = Table.ExpandTableColumn(RemoveColumn, "All", tcn)
in
    Expand

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Date	Details	Description	Withdrawn	Paid In	Balance		Date	Details	Description	Withdrawn	Paid In	Balance
2	4/1/2021	Online Transaction	Station					4/1/2021	Online Transaction	Station; Pub; VIA MOBILE - PYMT		1,000.00	1,525.10
3			Pub					4/1/2021	Charges	11DEC A/C xxxxxx	8.00		1,517.10
4			VIA MOBILE - PYMT		1,000.00	1,525.10		05/01/2021	OnLine Transaction	John Smith; WAGE; VIA ONLINE - PYMTB		715.00	2232.1
5	4/1/2021	Charges	11DEC A/C xxxxxx	8.00		1,517.10
6	05/01/2021	OnLine Transaction	John Smith
7			WAGE
8			VIA ONLINE - PYMTB		715.00	2232.1
9
Sheet1

 

[Nygie]

Thanks JGordon11. I have seen reference to power query earlier in my searches. On looking I am in luck having Excel 2010

 

[Nygie]

It worked a treat thank you, I tried following the code and its all greek to me. Never seen power query code before