I have used acrobat's "merge to excel" function, which results in a single sheet with a row for each question and a separate column of responses for each file.
This format has proven difficult to analyse, so in the past I've manually moved the data around so all the responses are in a single column, in order to be able to graph it / chuck into a pivot table, etc.
Image description: example demonstrating how data looks when merged from pdf (table A) and also how it looks after I've manually reformatted it (table B).
Now, this isn't a particularly elegant solution, but it has worked ok for our purposes to date. However, our volume of data is about to increase from half a dozen files to around 100, so I really don't want to be dealing with having to reformat data manually in that volume.
I know I could throw some VBA at the situation to automate reformatting the data, however I feel like this shouldn't be necessary at all - that I ought to be able to work with the data in its existing format using power query... however I just can't figure out how to make this work.
Can anyone please explain to me how to get data from table A in a way that can be used in PBI (or power pivot)? Or point me to any useful resources if this is well documented somewhere I've not managed to google my own way towards...
Many thanks in advance.
(nb: i've also asked this over on stackexchange, so please forgive me if you are seeing the question twice!)
