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.

[ishan_dania]

.

[grazitti_sapna]

 Hi @davidkelly6022 you can refer attached workflow for your reference.
Thanks