Parsing pdf into excel

[GScott]

What is a good way to parse pdf into excel?
It needs to be flexible, perhaps working from copied data from the pdf.

Thanks for any help

 

[alansidman]

Bring the PDF into Power Query Editor. Manipulate your data and close and load to Excel.

 

[GScott]

I need to provide better quality detail as this didn't really work.
The pdf contains credit card transactions and could look like this

Date Amount Description
30/07/2019 50.00 The description
goes over a couple
of rows I need to merge

Each month the statement will have different numbers of transactions however with each transaction having 3 lines of description.
Can power query be this flexible?
Do I need to recreate the query for each month or can I repurpose?

 

[GScott]

Just noticed my mail has formatted correctly. There are 3 columns and the description column contents runover 3 rows underneath one another

 

[alansidman]

Using XL2BB, (no pictures), upload a sample of 8-10 records and then mock up the expected results for those records.

Your current explanation is confusing and I cannot visualize your needs.

 

[Joe4]

I have removed the solution mark from this thread.
If your query has not been solved, please do not mark it as such until it has been.

 

[GScott]

Apologies for delay. XLS2bb took some work to install. You will notice that the description appears over 2 rows - I would like to merge these descriptions into one row

Power Query Output.xlsx
	A	B	C
1	Date	Amount	Description
2	31/10/2023	$11.61	Sakuramobairu
3			1,100.00 JPY Rate:0.010554
4	02/11/2023	$83.23	Holiday Inn Santiago A Santiago
5			47,016.00 CLP Rate:0.001770
6	02/11/2023	$25.77	Whittakers
7			28.03 NZD Rate:0.919372
8	02/11/2023	$20.23	Vantage Bar
9			22.00 NZD Rate:0.919545
10	09/11/2023	$16.06	Refugio Dickson Vertic Pto. Natales
11			9,326.00 CLP Rate:0.001722
12
13
14	Preferred way of data presentation
15	31/10/2023	$11.61	Sakuramobairu 1,100.00 JPY Rate:0.010554
16
Sheet1

 

[alansidman]

With Power Query

Book1
	A	B	C
1	Date	Amount	Description
2	10/31/2023	$11.61	Sakuramobairu
3			1,100.00 JPY Rate:0.010554
4	11/2/2023	$83.23	Holiday Inn Santiago A Santiago
5			47,016.00 CLP Rate:0.001770
6	11/2/2023	$25.77	Whittakers
7			28.03 NZD Rate:0.919372
8	11/2/2023	$20.23	Vantage Bar
9			22.00 NZD Rate:0.919545
10	11/9/2023	$16.06	Refugio Dickson Vertic Pto. Natales
11			9,326.00 CLP Rate:0.001722
12
13
14	Date	Amount	New Description
15	10/31/2023	11.61	Sakuramobairu 1,100.00 JPY Rate:0.010554
16	11/2/2023	83.23	Holiday Inn Santiago A Santiago 47,016.00 CLP Rate:0.001770
17	11/2/2023	25.77	Whittakers 28.03 NZD Rate:0.919372
18	11/2/2023	20.23	Vantage Bar 22.00 NZD Rate:0.919545
19	11/9/2023	16.06	Refugio Dickson Vertic Pto. Natales 9,326.00 CLP Rate:0.001722
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Amount", Currency.Type}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Date"}),
    #"Added Custom" = Table.AddColumn(#"Filled Down", "Custom", each if [Amount]= null then [Description] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Amount] <> null)),
    #"Merged Columns" = Table.CombineColumns(#"Filtered Rows",{"Description", "Custom"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"New Description")
in
    #"Merged Columns"

 

[alansidman]

deleted. Not applicable to correct solution

 

[GScott]

Thanks, that solved it.
However the PDF can have a variable amount of pages and tables in it, depending on the month (I receive these pdf's monthly). Sometimes I need the query to extract data from 2 pages and then the next month I need to extract data from 5 pages. Can this flexibility be built into the code?
Thanks