Halloween Candy Selection Tool

This project is designed to be a useful tool to help with providing 3 popular candies to give to visiting trick or treaters. The tool uses data from 85 different candies. The selection of the candies uses a weighted score which uses the win percent, the sugar percent and the price percent information.

Project Objective:

The primary objective was to create a tool to select the top three candies based on user selection of flavours and types to give to trick or treaters. There is a supporting page which provides an analysis of the candies based on the user selection of flavours and types.

Methodology:

1. I imported the data from the provided csv file into Power BI and checked the data for missing values.
2. I then created a Weighed Score Column using Power Query as shown here so you can see how the contributing information is weighted. = Table.AddColumn(Source, "Weighted Score", each (0.60 * [winpercent]) + (0.20 * [sugarpercent]) + (0.20 * [pricepercent]))
3. I then created a Rank column based on the Weighted Score. = Table.AddRankColumn( #"Changed Type2", "Rank", { "Weighted Score", Order.Descending }, [ RankKind = RankKind.Competition ])
4. Several Columns were renamed to make them easier to follow. For example the column peanutyalmondy was changed to Nuts and the pluribus column was changed to Packaged.
5. I created a table to store the Image URLs for each candy. This was performed manually as I could not get suitable results from using a web scraping script. Images were pulled from a wide range of different sources to get the clearest image.
6. As the dataset consisted of only 85 rows, I merged this table back into the Candy data table.
7. I then added a column that grouped the Sugar Percent into 3 groups Low Medium and High. I used this approach instead of using the group option in Power BI desktop as I could control the sort order. The sort order in the group option was in alphabetical order and not in the order I wanted which was Low, Medium High. = Table.AddColumn(#"Expanded candy-image-urls", "Sugar Group", each if [Sugar Percent] <= 0.33 then "Low" else if [Sugar Percent] <= 0.66 then "Medium" else "High")
8. This approach was taken to group the price percentage into 3 groups. For the same reason as the Sugar Group = Table.AddColumn(#"Added Sugar Group Column", "Price Group", each if [Price Percent] <= 0.33 then "Low" else if [Price Percent] <= 0.66 then "Medium" else "High")
9. I added Sugar Group Sort Order and Price Group Sort Order columns to the table using a conditional column = Table.AddColumn(#"Changed Type", "Sugar Group Sort Order", each if [Sugar Group] = "Low" then 1 else if [Sugar Group] = "Medium" then 2 else 3)
10. I then Added a Type column to the table to provide a means of analysing by type. = Table.AddColumn(#"Changed Type1", "Type", each if [Hard] = 1 then "Hard" else if [Bar] = 1 then "Bar" else if [Packaged] = 1 then "Packaged" else "Other")
11. I then created a Candy Flavours column which unpivoted the flavour columns for analysis purposes. As the dataset appeared to be modelled for machine learning as the flavour and type columns were dummy variables . The code for this flavours column is shown below,

let

`Source = #"candy-data",`

`#"Added Conditional Column" = Table.AddColumn(Source, "Type Sort Order", each if [Type] = "Bar" then 1 else if [Type] = "Hard" then 2 else if [Type] = "Packaged" then 3 else 4),`

`#"Changed Type" = Table.TransformColumnTypes(#"Added Conditional Column",{{"Type Sort Order", Int64.Type}}),`

`#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Rank", "Image_URL"}),`

`#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Sugar Percent", "Price Percent", "Win Percent", "Sugar Group", "Price Group", "Sugar Group Sort Order", "Price Group Sort Order", "Type", "Weighted Score", "Type Sort Order", "Candy Name", "Chocolate", "Fruity", "Caramel", "Nuts", "Nougat", "Crisped Rice Wafer", "Hard", "Bar", "Packaged"}),`

`#"Removed Columns2" = Table.RemoveColumns(#"Reordered Columns",{"Hard", "Bar", "Packaged"}),`

`#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Candy Name", "Sugar Percent", "Price Percent", "Win Percent", "Sugar Group", "Price Group", "Sugar Group Sort Order", "Price Group Sort Order", "Type", "Weighted Score", "Type Sort Order", "Chocolate", "Fruity", "Caramel", "Nuts", "Nougat", "Crisped Rice Wafer"}),`

`#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"Type Sort Order", "Weighted Score", "Type", "Price Group Sort Order", "Sugar Group Sort Order", "Price Group", "Sugar Group", "Win Percent", "Price Percent", "Sugar Percent", "Candy Name"}, "Attribute", "Value"),`

`#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Flavour"}, {"Value", "Has Flavour"}})`

in

`#"Renamed Columns`

14. I then created small supporting tables for each flavour and type that contained two columns and two rows. An example using the Nuts table is shown here. These tables were made by manually entering the values for the first table and then duplicating the query and changing the column names using the Advanced Query Editor. let

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WikwtVtJRMlSK1YlW8ssHMg2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Contains Nuts" = _t, Nuts_Key = _t]),

    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Contains Nuts", type text}, {"Nuts_Key", Int64.Type}})

in

`#"Changed Type"`

15. I then unticked the enable load option for the Candy Image URLs table as this table was not needed in the data model.

16. I then clicked close and apply to load the queries into the Power BI desktop model.

17. Before continuing I created an empty table to store the report measures called _Measures.

18. I then created relationships between the supporting tables the candy_flavours table and the candy data table.

19. Two more pages were added to the report to hold a welcome page and pages with visuals. The canvas size was set at 1,960 pixels wide by 1,100 pixels high. These dimensions were chosen to keep close to a 16:9 ratio and allow for the large number of slicers.

20. Visuals and Measures were created as needed to build the report.

21. After all visuals were created and placed. I used Canva to create a design of the same dimensions as the Power BI report.

22. I loaded backgrounds obtained from Creative Fabrica to use as report backgrounds. I don't normally use graphical backgrounds in my reports. I tend to use solid colours or gradient colours. But for this report I chose backgrounds that suited the Halloween theme for the report.

23. Canva was then used to create the shaped backgrounds for the visuals and added to the backgrounds. I used screenshots of my Power BI report to assist with placing the shapes.

24. The backgrounds were then downloaded and added to Power BI.

25. The visuals were adjusted to match the shapes on the background.

26. Bookmarks were created for the two pages of visuals and configured to reflect the default page view with no slicers selected. This was to assist with the clear slicer buttons to be added to the report.

27. Page navigation and Clear Filters buttons were created and configured.

28. The pages were renamed to suitable names

29. I hid all pages except for the Home page, which is the initial page to be viewed, this is to create a clean navigation system for the users.