Hello,
I've fallen foul of the conversion from Acrobat to XLSX/CSV, which is a bit temperamental.
In this case I'm trying to bring PDF bank statements into Excel and clean them up with Power Query. The issue is that the statement is formatted so that long fields are word-wrapped. When the file is exported each line is turned into a separate row, including the wrapped data. In other words, some records are contained in a single row while other records are split over two rows.
I've attached a sample of the original table and how I'd like the data to end up. The logic is along the lines of "If Type is null, then that row needs to be combined with the row above." and I'd welcome some suggestions, please.
Many thanks.
Pieter
Hi Pieter,
You can use the technique described here for the Complex Table to reference the row above/below in Power Query.
In your attached file I've inserted the formula. Note that Payee 07 is not consistent with the others where the amount is on the second row, therefore payee 7's reference isn't correct. Hopefully this is just an error when preparing your example data and the actual data is structured consistently.
Mynda
Hi Mynda,
That's absolutely great - thank you so much. I'm really enjoying my PQ course and hadn't quite got to this stage yet. You were quite right - I was a bit too hasty in creating my Payee07 lines.
I've also read the blog you referenced, which has opened my eyes to a whole new range of possibilities!
Many thanks again.
Pieter
