In this tutorial find out why you should document your cleaning steps? How to you keep track of your data cleaning steps? And more..
Cleaning and transforming data is a crucial step in data analysis but can also be the most time-consuming element. Often, our data set is incomplete, not in the most optimal structure, and could contain formatting errors. To correct these issues, we can use tools like Power Query to clean up our dataset, but there are often many steps involved in this process.
Power Query keeps a record of each of your cleaning steps, but the descriptions it provides are not always the easiest to interpret. Imagine coming back to the example below a few days or even a few weeks later; you wouldn't have the vaguest idea which cleaning steps were which.
Another issue is that there is no "undo" button, meaning if you delete a step, it is gone forever. To replace these steps, you can easily reapply them if the surrounding steps have sufficient commentary.
Another problem is that we may not always be responsible for a dataset over the long term. We may have to hand the project over, collaborate with others, or simply refer back to the dataset to make changes later on. In any case, documenting your cleaning steps is a great way to save yourself and your stakeholders time. Check out the example below where no commentary has been applied
Three ways to document your cleaning steps
1) Renaming Steps
Renaming your steps helps to provide additional detail to the step, making it easier to find the step you're looking for (You don't want to delete the wrong step for example). You can use special character or numbering to indicate similar steps. This also keeps your workspace looking tidy. Find a consistent format that works for you and your colleagues but make sure to reference the affected column for easy identification.
2) Additional Steps
Another popular method is by inserting a blank step. This step contains no clean step within it, but is easily renamed to something of your choice. It can be used to show the start or end of a series of similar steps. In the example below, we've created blank steps called "# Split Order ID" and "# Split Product ID" to do this.
You can do this by right clicking on any step and selecting "Insert Step After". From here a message may pop up suggesting like below. You can agree to this since your step will not impact any further steps. It’s just a blank step.
3) Additional Commentary
The third option is to include additional commentary via an info button.
To do this, right click on any clean step. Select properties and from here you can add more commentary to your clean step. Once complete you can hover over the info button to quickly display your commentary.
Final tips
It’s best to add commentary to our cleaning steps as we go, even if we’re short on time. You might you're find to add your commentary after your data cleaning is complete, but if you come across a mistake that needs to be corrected, insufficient commentary will have you referring back to your steps scratching your head with which steps is which.
Finally within your team or organization, try and stick to the same documentation standards, this makes it easier for the team to pick up pieces of work from others and make changes where required. This consistency will save everyone time in the long run.
Happy cleaning.