Data Cleaning / Sorting Help

Question

Hi!&nbsp;I have a spreadsheet that looks like this:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Model&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Model&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Model&nbsp; &nbsp; &nbsp;exterior A&nbsp; exterior B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exterior A&nbsp; exterior B&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exterior A&nbsp; exterior B&nbsp;&nbsp; &nbsp; &nbsp;exterior C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; exterior C&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;exterior C&nbsp; &nbsp; &nbsp;cost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;cost&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SF1:&nbsp; &nbsp;$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#2:&nbsp; &nbsp;$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#3:&nbsp; &nbsp;$&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; $&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;#&nbsp;All of these reports come in the same way. However some may have more models than the other -&nbsp;is there an easy way to clean this type of data? I would like the output to be cost and SF by each model and exterior (you can have two models with the same exterior and vice versa). Any thoughts?&nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;

v-jiascu-msft · Answer

Hi&nbsp;Tylerspears27,&nbsp;Is your spreadsheet like below? I afraid it's hard to clean. Why are these "exterior *" in the header?&nbsp;Best Regards,Dale

tylerspears27 · Answer

Yes. That is correct - It is just how the report is generated making it very tough to clean and upload.

I am thinking of deleted those exterior rows out then to reference back to another sheet that has a list of all models with all exteriors from that source. Does this make sense & will work?

Forum Discussion

Data Cleaning / Sorting Help

2 Replies

Related content

DAX measures Clean-up in data model?

Does data cleaning automatically happen monthly?

Cleaning Data In PowerBi

Cleaning Messy Data

Sorting