Preppin' Data

 Challenge by: Jenny Martin I spent my International Women's Day being really fascinated by the Gender Pay Gap Bot on Twitter. I decided to look at the publicly available data that it was based on and found it a little confusing. It made me realise that the thing I enjoyed most about the Pay Gap Bot, was that it made it the insight from the data clear in a succinct manner. So let's do that for the historical data too!  Inputs We're using the data currently available on the Gender Pay Gap Service from 2017 to 2022:  There are 5 input files. Requirements Input the data Combine the files Keep only relevant fields Extract the Report years from the file paths Create a Year field based on the the first year in the Report name Some companies have changed names over the years. For each EmployerId, find the most recent report they submitted and apply this EmployerName across all reports they've submitted Create a Pay Gap field to explain the pay gap in plain English You may en...

Challenge By: Jenny Martin One of the best things about being a Dr Prepper is that people are always bringing interesting datasets to your attention. A little while ago, Tableau Zen Master Lorna Brown  showed me a dataset with all kinds of information on tourism in the Maldives . This database has a lot of data on different Key Economic Indicators, but as you can imagine, it also has a bit of a quirky structure! For inspiration as to why we might want to clean this data up, check out Lorna's viz below: Link to viz Input Our input is very wide this week, with 136 fields and only 28 rows. It covers tourism in the Maldives from 2010 to 2020. The source of this data is here but you can download it in the usual way from here . Requirements Input the data Pivot all of the month fields into a single column ( help ) Rename the fields and ensure that each field has the correct data type Filter out the nulls ( help ) Filter our dataset so our Values are referring to Number of Tourists Our g...

Would you believe that Chin & Beard Suds Co have encountered yet more messy data? It seems someone was trying to be helpful by creating an aggregated view of sales per week for each scent of soap. However, in doing so we've lost the lower level of detail of the product sizes that make up these sales for each scent. We really need this for other analysis we've been carrying out! Fortunately, we know what percentage of sales each product size makes up for each product in each week. Unfortunately, the data isn't stored in a way that will make it easy to join all the necessary information together. Inputs There is 1 input file with 3 sheets: Total Sales by Week for each Scent Percentage of Sales for different product sizes per product per week. A lookup table for linking Scent, Product ID and Size Requirements Input data Our final output requires the Date to be in in the Year Week Number format.  (Check out 2020.1.4 for an easier way to clean ...

The full solution can be seen below and downloaded here . The full workflow. Converting date and time to DateTime Within the Manual Capture Error List   ( MCEL ) we were given [Start Date] and [Start Time] , but really we want [Start DateTime] . There were a number of ways to go about this, including converting to string and using DATEPARSE() and adding the dates together using DATEADD() . I think the simplest solution however was to use the MAKEDATETIME() function as below: MAKEDATETIME([Start Date], [Start Time]) The MAKEDATETIME() function takes the date part of the first parameter and combines it with the time part of the second parameter. In our case that means it combining the date from [Start Date] and the time from [Start Time] together. No need to do any tricky parsing or conversions as it does it for us! Filtering out overlapping errors This was the main challenge of the week – it was tricky to even figure out if it was possible. In the end we’ve co...