The first #PreppinData challenge for 2020 was to recreate Ryan Sleeper’s financial statement table using data from the fictitious Chin & Beard Suds Co.
To do this we need to:
- get the profit totals
- get the profit subtotals
- update the blanks with the total & subtotals
- indent the 2nd & 3rd level data
This is the initial view of the data. Based on the naming convention of the Item my first thought was to split off the number portion of the field and use that as a way to create a hierarchy.
I used the custom split tool to parse the field off a space and took the first field. I trimmed any extra spaces and renamed this field Item Id. These are the calculations:
I then created by hierarchy levels taking the left X number of characters from the new item id field. My thought was I would use these to get the totals & subtotals. These are the fields I created for the hierarchy:
I also created a new field with the indented item names:
Format Item Name
IF LEN([Item ID]) = 2 THEN [Item]
ELSEIF LEN([Item ID]) = 3 THEN SPACE(5) + [Item]
ELSEIF LEN([Item ID]) = 5 THEN SPACE(10) + [Item]
After I had my levels I then created two aggregates to get the totals and subtotals. The first one sums the profit by my new top level field and the second one sums the profit by the new second level field. I joined both of these aggregates back to the prior step where the top level in to totals aggregate = the item id & where the second level = the second level.
The last step is the clean up step. I this step I have 11 changes to the joined data.
- remove the duplicated fields from the joins.
- merge the profit field from the initial step with the profits from the aggregates
- rename the merged profit fields to profit
- created a calculated field to get the length of the item id field to sort my rows correctly
- renamed the Format Item Name field to item name
- removed any remaining unnecessary fields
This was a great challenge to kick off the 2020 #PreppinData series. I love the formatting idea from Ryan and have a few ideas of how I can implement both Ryan’s table and this PreppinData challenge in my day to day work.
If anyone is interested in getting a copy of my flow please let me know. I am more than happy to share my approach.