Part $3$: Business Intelligence with Power Query & Power Pivot

Joan Amari is a sales executive for Yummy Frozen Yogurt, a chain of frozen yogurt stores. Joan is responsible for overseeing $20$ franchises in California. Joan wants to compare average customers per day at the franchises over the past three years and determine whether factors such as location, date, and weather play a significant role in the volume of customer traffic. She has data that contains over $21,000$ records from the daily sales in the $20$ stores from the past three years.

$1.$ Use Power Query to access the Support$\_$EX$\_10\_$Yogurt Access database, creating a connection only to the Sales and Stores tables into the workbook$$s Data Model.

$2.$ You would like to create a Column with the abbreviation for monthly sales, using the first $3$ characters such as Jan, Feb, Mar. Complete this using $2$ steps:

a$.$ Within Power Query, add a new Column to the Sales table using the Date command on the ribbon to display the name of the month $($January$,$ February, March$).$

b$.$ Add another Column using the Extract command to insert the first $3$ characters of the month $($Jan$,$ Feb, Mar$).$ Rename this Column Month Abbreviation.

$3.$ Open Power Pivot to view the Data Model in Diagram View.

a$.$ Create a relationship between the Sales and Stores table through the Store ID field.

b$.$ In the Sales table, create a hierarchy named Calendar containing the Year and Month Abbreviation fields. Return to the workbook.

$4.$ Go to the Datasets worksheet and review the fields in your dataset. Joan wants to analyze the average number of customers served by the Yummy Frozen Yogurt by Temperature, Rainfall and Month. Create $3$ Pivot Charts to analyze these categories. Use the Staging Worksheet to create your Pivot Tables and charts.

a$.$ Create $3$ different types of charts $($ie: not all clustered Column$)$ to evaluate Temperature, Rainfall $($Heavy vs Normal Rainfall$)$ and Calendar.

b$.$ The Calendar chart must use a hierarchy drilled down into the $2021$ Year category to the level of month

c$.$ Temperature chart must use a scatter chart to analyze the relationship between average high temperature and average number of customers. Because PivotCharts cannot be created as scatter charts, you need to create your pivot table first. Then, copy the data and use Paste Link to paste the copied cells. Use the copied cells to create your scatter chart.

d$.$ Use best practices for chart making,

e$.$ Move your $3$ pivot charts to the Sales Report worksheet, in the highlighted sections.