Power query

Bassmansam · 13 Dec 2021 at 16:03

Hi

I am importing data from a PDF file into excel, there are 60 tables, on some tables the column names aren't being picked up correctly. Is there a way to change column names for every table in one go please?

gpuerrilla · 13 Dec 2021 at 16:20

From Folder. Drop PDF(s) into the folder, in your PQ steps force a rename on the columns that seem problematic?

Bassmansam · 13 Dec 2021 at 17:20

gpuerrilla said:
From Folder. Drop PDF(s) into the folder, in your PQ steps force a rename on the columns that seem problematic?

huh? Its just one pdf with 60 tables.

What i’m asking is, can i rename the columns in all tables at once?

gpuerrilla · 13 Dec 2021 at 17:48

Are all the tables identical in layout?

Bassmansam · 13 Dec 2021 at 17:54

gpuerrilla said:
Are all the tables identical in layout?

they will be once i have done some cleaning up. It’s been inconsistent, for example on certain tables its decided to make more columns than others even though the pdf columns are consistent.

gpuerrilla · 13 Dec 2021 at 17:59

Be handy if I could see the examples.

Is this a one off task and is it only available in PDF?

Bassmansam · 13 Dec 2021 at 18:08

gpuerrilla said:
Be handy if I could see the examples.

Is this a one off task and is it only available in PDF?

ah its for a new job i’ve started so i don’t feel comfortable sharing. I have to do this 4 times a year. I kind of know what i’m doing but its going to take a while to tidy up/ edit column names manually.
Thanks for your help

gpuerrilla · 13 Dec 2021 at 21:46

Could you use a test file to try this method @Bassmansam?

OspreyO · 13 Dec 2021 at 22:27

You could loop though in VBA afterwards easily enough.

Never imported tables from a pdf. Can you you do a transformation

Rich · 19 Dec 2021 at 23:02

I can’t imagine importing from PDF is ever going to be a good idea, but when I’ve had no choice I’ve used https://tabula.technology/

OspreyO · 20 Dec 2021 at 00:46

I've never found PDF robust as format for data transformation. But it gets thrown at you constantly. In Power Apps, Power Automation there is ocr scanning functionality to read in forms and tabular data. I've never used it personally.