I have a file that is an extract from a financial system formatted as follows:
Note - this is dummy data from a test environment.
For each description row, you can see the data wraps onto a 2nd row. The 2nd row is key as it identifies the location the movement is from (BNK, BRT, OAK, DDS).
In order for me to summarize this properly, I need a way to "unwrap" that 2nd row so that somehow I can get that description column all on 1 row.
The source system is not flexible enough to allow that range to be expanded...it is capped at 40 characters.
The other issue the data does not always wrap....it just does for particular rows...they key identifier would be that opening balance field....where there is a value, that denotes a new line.
If power query can resolve this issue and someone can assist I will truly be impressed !
Hi steve,
Please provide a sample file in the format you receive the data (CSV, Text etc.). I don't have time to recreate your data from scratch.
Thanks,
Mynda
Hi Mynda - thanks for your response.
A sample is attached.
Note - the actual extract has no extension - I had to add ".txt" onto it in order to attach it in the forum.
The files are normally produced to be viewed via a web browser.
Another note is I did strip out some information (company name etc, that does not impact the formatting of the file). I also wanted to point out this is dummy data from a test system (not real financial information)
Hi Steve,
Thanks for providing the data. The technique you can use to move the data on the second row up, so all data is in one row, is Modulo with unpivot. You can see a tutorial on that here:
https://www.myonlinetraininghub.com/reformat-excel-reports-with-power-query
The requirement for this technique to work is that you remove all of the noise from your data so all you're left with are the sets of 2 rows you want to fix. In the attached file I've done that using Filters. Note: You'll need to edit the file path and file name in the M code of my file to point at where you saved your text file.
If you want to keep the data that has been removed by the filters, then you create 2 queries; one for the data that wraps onto two rows and one for everything else by filtering out the data that's in the first query, you then append those queries back together once the data is all cleaned up.
I hope that makes sense. Let me know if you get stuck.
Mynda
