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 ro...
Thanks for responding again. Unfortunately the data is sometimes over 1 row, sometimes 2 and sometimes 3 (depending on what elements are updated).
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