Convert PDF with PQ: splits text into two rows

[JuliaGr]

Hello,

I am converting PDF with Power Query and run into the problem.
The conversion separates text into two rows for the column "details" and keeps the rest columns blank.
The issue is not regular; sometimes it appears after every row, other times after couple of rows.

How may combine this rows?

Thank you in advance!

Page	Date	DR CR	Details	Amount
1	26/08/2021​	C	PAYMENT FROM	1000​
			BOB
1	26/08/2021​	C	PAYMENT FROM	140​
			BOB
1	30/08/2021​	C	PAYMENT FROM	15​
			BOB
1	30/08/2021​	C	REAL TIME TRANSFER FROM	300​
			BOB
2	31/08/2021​	D	BALANCE BROUGHT FORWARD	0​
2	31/08/2021​	C	REAL TIME TRANSFER FROM	370​
			CAPITEC
2	31/08/2021​	D	MONTHLY FEE	-100​
			ACC
2	04/09/2021​	C	CREDIT TRANSFER	120​
2	04/09/2021​	C	REAL TIME TRANSFER FROM	350​

 

[JGordon11]

If there is always a Page number ([Page]<>null) on the rows you want to keep, try:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tblAddIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    tblAddCustom = Table.AddColumn(tblAddIndex, "Custom", each try if tblAddIndex[Page]{_[Index]+1} = null then 
        _[Details] & " " & tblAddIndex[Details]{_[Index]+1} 
        else if [Page]<>null then [Details] else null otherwise [Details]),
    tblRemoveNulls = Table.SelectRows(tblAddCustom, each [Custom] <> null),
    tblRemoveOrigDetails = Table.RemoveColumns(tblRemoveNulls,{"Details","Index"}),
    tblRenameCustom = Table.RenameColumns(tblRemoveOrigDetails,{{"Custom", "Details"}}),
    tblReorder = Table.ReorderColumns(tblRenameCustom,Table.ColumnNames(Source))
in
    tblReorder

Book5
	A	B	C	D	E	F	G	H	I	J	K
1	Page	Date	DR CR	Details	Amount		Page	Date	DR CR	Details	Amount
2	1	26/08/2021	C	PAYMENT FROM	1000		1	26/08/2021	C	PAYMENT FROM BOB	1000
3				BOB			1	26/08/2021	C	PAYMENT FROM BOB	140
4	1	26/08/2021	C	PAYMENT FROM	140		1	30/08/2021	C	PAYMENT FROM BOB	15
5				BOB			1	30/08/2021	C	REAL TIME TRANSFER FROM BOB	300
6	1	30/08/2021	C	PAYMENT FROM	15		2	31/08/2021	D	BALANCE BROUGHT FORWARD	0
7				BOB			2	31/08/2021	C	REAL TIME TRANSFER FROM CAPITEC	370
8	1	30/08/2021	C	REAL TIME TRANSFER FROM	300		2	31/08/2021	D	MONTHLY FEE ACC	-100
9				BOB			2	04/09/2021	C	CREDIT TRANSFER	120
10	2	31/08/2021	D	BALANCE BROUGHT FORWARD	0		2	04/09/2021	C	REAL TIME TRANSFER FROM	350
11	2	31/08/2021	C	REAL TIME TRANSFER FROM	370
12				CAPITEC
13	2	31/08/2021	D	MONTHLY FEE	-100
14				ACC
15	2	04/09/2021	C	CREDIT TRANSFER	120
16	2	04/09/2021	C	REAL TIME TRANSFER FROM	350
Sheet1

 

[JuliaGr]

Hi JGordon11,
Thank you for your help! it worked well and gave me ideas how to proceed with other files

 

[JGordon11]

Here's another way, it's shorter but may not be faster:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tcn = Table.ColumnNames(Source),
    col = List.PositionOf(tcn,"Details"),
    lstCombine = List.Generate(()=> 0, (x)=> x < Table.RowCount(Source), (x)=> x +1, 
        (x) => Source[Details]{x} & (try if Source[Page]{x+1} = null then " " & Source[Details]{x+1} else "" otherwise "")),
    tblFromColumns = let ttc = Table.ToColumns(Source) in Table.FromColumns(List.FirstN(ttc,col) & {lstCombine} & List.Skip(ttc,col+1),tcn),
    tblOut = Table.SelectRows(tblFromColumns,each ([Page] <> null))
in
    tblOut

 

[XL Pro]

Not knowing how all the data looks like, one caveat you have to worry about that using the Index approach could have a performance issue on larger datasets.
Again, without seeing the rest of the data, here's another solution:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Conditional Column" = Table.AddColumn(Source, "Custom", each if [Page] = null then [Details] else null),
    #"Filled Up" = Table.FillUp(#"Added Conditional Column",{"Custom"}),
    #"Merged Columns" = Table.CombineColumns(#"Filled Up",{"Details", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Details"),
    #"Filtered Rows" = Table.SelectRows(#"Merged Columns", each ([Page] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Page", "Date", "DR CR", "Details", "Amount"})
in
    #"Removed Other Columns"