How to extract data from a new pdf file (weekly) and append that data to a dataset?

[Anonymous]

Hi everyone, 

I want to automate a price update that I make weekly. Every week I received 5 pdfs (similar look every week) with this week latest prices and now I manually type the latest prices into a document. Is there anyway that I can upload the pdf, extract the data and append that data to a dataset instead?

Thank you in advance.

Sincerely, Signe

[Anonymous]

Dear @v-yanjiang-msft 

Thank you for the answer and sorry for the late reply, I had not seen the reply e-mails. 

I think your solution is correct given the formulation of my question, but I need bit more help in order to solve my problem. 

So building on to your example, I don't just need the 4. I need the entire sales column from Page001 to be transposed into a row in the Query table and then with a date attached to the same row. So like this:

Date		A	B	C	D
2022-12-01		1	2	3	4

And then I need to automate the process as I will receive a new pdf each week with new 'sales' data and will then need a new row in the query table with that week's data and so on. So like this:

Date		A	B	C	D
2022-12-01		1	2	3	4
2022-12-08		1.5	2	3.25	5

Can you help me with this?

[v-yanjiang-msft]

Hi @Anonymous ,

Is your problem solved? If so, would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirment will find the solution quickly and benefit here, thank you!

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, I think you can do it. I give an example to illustrate the steps.

1.Connect the PDF in Power BI Desktop through Get Data>PDF.

2.It is presented in table format when connected.

3.Now you can reference values in the table. For example you want to get the Sales of Category D, create code like this:

Table.SelectRows(#"Page001",each [Category]="D")[Sales]{0}

Result:

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.