Upload scrambled data (PDF) via Power Query

[Anonymous]

On a daily basis, I receive a PDF report that I would like to upload via Power Query.

However, the data is not always structured the same way. After the necessary cleaning, this is where I stranded:

What needs to happen is that the value in column 9 should end up in column 8 (which I currently do by mergng the 2 columns).

And the final result should look like this ((column 2 and column 9)

However, as the number of columns isn't always the same, this provokes errors when I get new pdf's in:

What is also good to know is that sometimes all data is nicely under each other (same column), sometimes it's not. And also the number of records that should be extracted from the pdf, is not always the same either (sometimes it's 1, sometimes it's 4, ...)

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

To my knowledge, we could not smartly know where the data structure has changed.

If the original column names change, the subsequent reference steps should be manually changed accordingly.

For more detailed explanation, please refer to: Dealing with errors - Power Query | Microsoft Docs

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