I have unstructured data in a text file taken from a mainframe. I have used PQ to import the data successfully, however the info from each line is spread across two cells vertically. For example I have Joe Bloggs but Joe is in the first cell and Bloggs is in the cell below. How do I combine these cells together? I've been told that it would be something to do with using two index columns but I'm not sure how that works.
The data also comes in "pages" so the details of the file are scattered throughout whenever the system creates a new page. I need an automated way to get rid of those too as the placement could be different each time.
I have included the sample data text file and the beginnings of my PQ file.
I'm using Excel 2016.
thanks in advance
Paddy
Hi Patrick,
Have a look at the attached file and see if you can follow. Duplicated your query and created my own with some extra clean-up steps, resulting in a table with two rows per Reference. Then added two index columns. One zero-based (I named the step Index0), the other one-based (I named the step Index1). Now you can merge the table with itself, "linking" Index1 to Index0. Expand, the merged tables and do some more clean-up.
Riny
PS: Obviously, you need to point the Source step to your own txt-file.
Thanks Riny,
This was a huge help and I have managed to recreate this on the actual data.
Glad I could help!
