Preppin' Data

Each week I normally like to outline some key points and main learning objectives, however today I’m offering a full walk-through of the solution, so everyone can understand the logic involved at each step. You can see our solution below and download it here . Before I go into the full walk-through, I’d first like to provide some reassurance that this is a difficult challenge and our trim and streamlined solution was the result of iteration and feedback! If anyone got a flow that looks anything like ours on their first try then we are seriously impressed. To add context, here are some of the original solution flows we came up with reaching the final flow seen above. -------------------- -------------------- . When trying to solve a data prep obtaining any solution is a great start as you can begin inspecting, refining, and improving it. As with many problems, iteration is a key to success! Now onto the walk-through. Cleaning the Data and Adding Flags First we...

The full solution workflow can be viewed below and downloaded here . The full workflow solution There are four main learning outcomes of this week’s Preppin Data: 1. Using the DATEPART() function. 2. Flagging issues and counting the frequency of these issues. 3. Using different levels of aggregation on the same data set. 4. Using the ROUND() function. There’s also a neat little bonus learning objective to save yourself some time when repeating common process snippets. 5. Saving time by copying and pasting workflow changes and tools. Using the DATEPART() function In order to get the week, month, and quarter numbers for our data we can use the aforementioned DATEPART() function. This function takes two parameters:  DATEPART(‘name of the date part you want’, [the Date Field you want the date part for]) In our case this means we can use the following function calls to get the numbers we desire: DATEPART(‘week’, [Date] ) DATEPART(‘mont...

Following the success of Chin & Beard Suds Co, we’ve been informed by our distribution company that they’re having some technical difficulties, so we’d like to step in and offer our professional assistance. The Scenario A shipping distribution company transports valuable cargo for customers all around the world using three different types of boat. They keep the basic details for each departure in a table called Departure Details. A snippet of the Departure Details table. They sell and allocate space on their departures based on the weight and volume of the cargo. They keep the details of each allocation in a table called Allocation Details. A snippet of the Allocation Details table. In the last 3 months they’ve begun noticing issues where more weight or volume is being allocated than the boats can actually carry. This means customers are turning up at the port with cargo which the company is then having to turn away and refund. They want to identify and flag departures...