Forum Discussion
Messy Raw Data
hi, I'm hoping somebody here is able to guide me on the best way to transform this data coming from a very messy raw data file. The main problem is that the column headers & data are spread over 2 rows.
The objective is to transform the data & have 1 row of headers & data (per order)
I have the raw data in 2 different formats (txt and xls list), but neither one seems to be easier to work with than the other
Thanks in advance for anybody who was able to assist on this one
5 Replies
Double row header is not a problem:
1. Tanspose data
2. Merge first two columns
3. Transpose data
4. Promote first row as headers
oh sorry, i missed the part on rows being affected too. Urgh, horrible situation. Okay,
1. Bring in the data and make sure its in an order where the rows are in their order ie row 1a, row 1b, row row 2a, row 2b etc.
2. Add index column starting at 1 and filter for even numbers only,
3. Duplicate query and change the filter step to odd numbers only
4. On the 'odds' table create a custom column which is index +1.
5. Merge the two tables using the index from the 'evens' and the custom from the 'odds'
6. Expand out the data.
So long as the sorting in the first step is solid. this will work fine. Do the first suggestion too, sorting out the double row headers.
