Messy Raw Data

[Anonymous]

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

[samdthompson]

Double row header is not a problem:

1. Tanspose data

2. Merge first two columns

3. Transpose data

4. Promote first row as headers

[Anonymous]

I don't believe that handles the double rows of data

[samdthompson]

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.

[Anonymous]

Thanks for responding again. Unfortunately the data is sometimes over 1 row, sometimes 2 and sometimes 3 (depending on what elements are updated).

[samdthompson]

Ah right.

1. When you bring it in the first column will have the DO/SO item wil nulls for any 2nd or 3rd line.

2. Use the fill down option to populate those down the column.

3. Group by the DO/SO column and get a count of rows for each and expand out again.

4. Merge all the columns other than DO/SO and the count column together and then pivot them by the count.

5. Split the columns by what ever delimiter you chose when you merged them

6. I cant quite imagine how many columns you will end up with after this process but there will be a bunch to delete