Data Clensing

davidkelly6022

I am dealing with some messy data and I was wondering how I would be able to clean it up. The data comes in a format such as the one below with headers and sub-headers starting in different columns but the numeric figures all starting in the same column. 

Sales				500
	Product sales			100
		Non-product sales		400

I was hoping to be able to pull the headers and sub-headers to the first column, the numeric figures to the second column, and remove null columns so the format would look as it does below. 

Sales	500
Product Sales	100
Non-Product Sales	400

DataNath

If your actual data is like the example provided then something like this ought to do the trick @davidkelly6022 - could probably be a little more concise but hopefully makes things easier to follow!

We basically pivot all of the dispersed sub-headers into a single column, sort them so that those that are populated will always appear above the empty/null cells and then un-pivot (Cross-Tab), selecting First as the aggregation so it picks the populated record i.e. the sub-header. When plugging in your real data you'll just have to ensure that the Group By in the Transpose and Cross-Tab are the RecordID and the field with your numerical values.

Data cleansing sub-headers.yxmd