{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Score","Instructions","Last Customer Purchase","Last Date-Time for 911 Call"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Score","index":0,"n_rows":42,"n_cols":5,"rows_kept":42,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Graded Element","Possible Points","Earned Points","Comment"]
[null,"On the sheet Last Customer Purchase, use the table of data and create a report that shows that last date that a customer made a purchase.",{"$f":"100/ROWS($C$4:$C$35)","v":3.125},{"$f":"C4","v":3.125}]
[null,"Add Customer to Rows area",3.125,{"$f":"C5","v":3.125}]
[null,"Drag Date to Values area",3.125,3.125]
[null,"Change calcaution to Max.",3.125,3.125]
[null,"On the sheet Last Date-Time for 911 Call you will create a report that shows that last date-time that a type of 911 call was made by Year (picture below). Steps are listed here:",3.125,3.125]
[null,"The 911 call data must be downloaded from this Seattle City web site: https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj",3.125,3.125]
[null,"Use the From Text/CSV button to import the CVS text file into the Power Query Editor.",3.125,3.125]
[null,"Name the table f911Calls.",3.125,3.125]
[null,"Add a Date field to the table using these steps: select Datetime field, click on Add Column tab in Power Query Ribbon, in the From Date & Time group, click the Data dropdown, then click on Data Only.",3.125,3.125]
[null,"After you do that you will have a new field with just dates only",3.125,3.125]
[null,"Make sure all fields have correct data type.",3.125,3.125]
[null,"Use Power Query to import it into the Power Pivot Data Model. Name the table f911Calls BEFORE you load it to the Data Model.",3.125,3.125]
[null,"Go to the Power Pivot for Excel Window.",3.125,3.125]
[null,"Make sure you are in Data View (Home tab of Power Pivot Ribbon, View group), and your cursor is in one cell in the Datetime field.",3.125,3.125]
[null,"To create a Data Dimension Table, click on the Design tab in the Power Pivot Ribbon, then in the Calendars group, click the Data Table dropdown arrow, then click New.",3.125,3.125]
[null,"Rename the tab for this data table to dDate.",3.125,3.125]
[null,"To create a relationship, go to the Home tab of Power Pivot Ribbon, then to View group, then click Diagram View. Then drag the Data field from the fact table to the dimension table.",3.125,3.125]
[null,"Back in Data View, create a DAX Measure to find last date time. The DAX formula is: Last Date-Time:=MAX(f911Calls[Datetime])",3.125,3.125]
[null,"Make sure Measure has a date time number formatting",3.125,3.125]
[null,"Create a report as seen Below =>",3.125,3.125]
[null,"Type field into Rows Area",3.125,3.125]
[null,"Data Hierarchy into Columns area",3.125,3.125]
[null,"Measure for last date into Values area.",3.125,3.125]
[null,"Add a Slicer for Values (and show multiple columns in Slicer).",3.125,3.125]
[null,"Below picture shows the last 911 call for Tunnel Aid for each year.",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[null,"Other graded elements",3.125,3.125]
[]
[null,"Late?","No"]
[]
[null,"Total Possible Points",{"$f":"SUM(C4:C35)","v":100}]
[null,"Total Earned Points",null,{"$f":"IF(C37=\"No\",1,0.75)*SUM(D4:D35)","v":100}]
[]
[null,"Be Sure to Scroll Up To See Full Score Sheet"]

{"sheet":"Instructions","index":1,"n_rows":29,"n_cols":4,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,"Product 4 Instructions:"]
[null,1,"On the sheet Last Customer Purchase, use the table of data and create a report that shows that last date that a customer made a purchase."]
[null,2,"On the sheet Last Date-Time for 911 Call you will create a report that shows that last date-time that a type of 911 call was made by Year (picture below). Steps are listed here:"]
[null,3,"The 911 call data must be downloaded from this Seattle City web site: "]
[null,null,null,"https://data.seattle.gov/Public-Safety/Seattle-Real-Time-Fire-911-Calls/kzjm-xkqj"]
[null,4,"When you go to web site click the Export button in upper right corner, then click CSV Excel."]
[null,null,"The file name is: Seattle_Real_Time_Fire_911_Calls.csv. It has over 1 millions rows of data."]
[null,5,"Use the From Text/CSV button to import the CVS text file into the Power Query Editor."]
[null,6,"Name the table f911Calls."]
[null,7,"Add a Date field to the table using these steps: select Datetime field, click on Add Column tab in Power Query Ribbon, in the From Date & Time group, click the Date dropdown, then click on Date Only."]
[null,null,"After you do that you will have a new field with just dates only"]
[null,8,"Make sure all fields have correct data type."]
[null,9,"Use Power Query to import it into the Power Pivot Data Model. Name the table f911Calls BEFORE you load it to the Data Model."]
[null,10,"Go to the Power Pivot for Excel Window."]
[null,11,"Make sure you are in Data View (Home tab of Power Pivot Ribbon, View group), and your cursor is in one cell in the Datetime field."]
[null,12,"To create a Data Dimension Table, click on the Design tab in the Power Pivot Ribbon, then in the Calendars group, click the Data Table dropdown arrow, then click New."]
[null,13,"Rename the tab for this data table to dDate."]
[null,14,"To create a relationship, go to the Home tab of Power Pivot Ribbon, then to View group, then click Diagram View. Then drag the Data field from the fact table to the dimension table."]
[null,15,"Back in Data View, create a DAX Measure to find last date time. The DAX formula is: Last Date-Time:=MAX(f911Calls[Datetime])"]
[null,16,"Make sure Measure has a date time number formatting"]
[null,17,"Create a report as seen Below =>"]
[null,null,"Drop Type field into Rows Area"]
[null,null,"Data Hierarchy into Columns area"]
[null,null,"Measure for last date into Values area."]
[null,null,"Add a Slicer for Values (and show multiple columns in Slicer)."]
[null,null,"Below picture shows the last 911 call for Tunnel Aid for each year."]
[null,null,"Your data will be slightly different because this City of Seattle data source updates every 5 minutes."]
[null,18,"Your result will have different dates than the cones seen in the picture."]

{"sheet":"Last Customer Purchase","index":2,"n_rows":61,"n_cols":7,"rows_kept":61,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[null,"Date","Sales Rep","Customer","Units","Product","Net Revenue"]
[null,"2020-12-10","Tynia Malone","Thriftway",1,"Vegetables",43.69]
[null,"2020-11-14","Chantel Mims","Thriftway",13,"Vegetables",441.35]
[null,"2020-10-30","Janis Figueroa","PCC",9,"Vegetables",393.21]
[null,"2022-12-08","Hien Pham","Safeway",6,"Dairy",256.14]
[null,"2020-03-31","Kiki Sho","Target",425,"Dairy",10432.370000000001]
[null,"2022-07-16","Hien Pham","PCC",5,"Vegetables",139.75]
[null,"2021-08-03","Hien Pham","Whole Foods",10,"Fruit",339.5]
[null,"2020-08-24","Hien Pham","Whole Foods",250,"Dairy",4891.25]
[null,"2021-05-04","Tynia Malone","Walmart",375,"Fruit",5379.84]
[null,"2022-12-07","Chantel Mims","Safeway",500,"Fruit",6885.63]
[null,"2021-10-08","Tynia Malone","Lucky's",575,"Fruit",8249.09]
[null,"2021-07-13","Tynia Malone","Walmart",41,"Dairy",1183.1600000000001]
[null,"2020-03-22","Chantel Mims","Safeway",26,"Dairy",595.6]
[null,"2022-03-01","Janis Figueroa","Safeway",100,"Vegetables",1886.5]
[null,"2020-07-17","Hien Pham","Safeway",100,"Dairy",2988.3]
[null,"2020-08-25","Kiki Sho","Walmart",4,"Fruit",178.76]
[null,"2020-03-02","Chantel Mims","Thriftway",-17,"Dairy",-577.15]
[null,"2022-12-09","Tynia Malone","Whole Foods",350,"Fruit",8792.61]
[null,"2021-06-13","Kiki Sho","Thriftway",125,"Other",3910.38]
[null,"2022-08-14","Tynia Malone","Target",5,"Fruit",213.45]
[null,"2022-08-05","Chantel Mims","QFC",7,"Dairy",188.65]
[null,"2021-01-01","Tynia Malone","Safeway",35,"Other",831.51]
[null,"2020-12-30","Tynia Malone","Lucky's",20,"Fruit",719]
[null,"2020-03-28","Janis Figueroa","Walmart",1,"Dairy",23.95]
[null,"2021-06-24","Tynia Malone","Lucky's",7,"Other",202.65]
[null,"2021-08-04","Chantel Mims","PCC",125,"Vegetables",2358.13]
[null,"2022-10-22","Tynia Malone","Walmart",17,"Fruit",424.15]
[null,"2021-01-12","Chantel Mims","QFC",7,"Dairy",181.65]
[null,"2021-11-12","Kiki Sho","Whole Foods",175,"Dairy",4158.88]
[null,"2021-05-19","Chantel Mims","QFC",23,"Fruit",619.85]
[null,"2020-05-15","Chantel Mims","Thriftway",15,"Fruit",374.25]
[null,"2022-03-01","Chantel Mims","QFC",325,"Fruit",6718.16]
[null,"2021-03-10","Hien Pham","Safeway",12,"Vegetables",512.28]
[null,"2022-02-20","Chantel Mims","Walmart",7,"Dairy",188.65]
[null,"2022-11-08","Janis Figueroa","Lucky's",5,"Vegetables",179.75]
[null,"2022-06-28","Janis Figueroa","Target",125,"Dairy",2445.63]
[null,"2021-10-03","Tynia Malone","PCC",-48,"Fruit",-934.2]
[null,"2020-06-16","Tynia Malone","PCC",7,"Vegetables",202.65]
[null,"2021-11-27","Tynia Malone","Safeway",5,"Dairy",129.75]
[null,"2022-09-21","Janis Figueroa","QFC",375,"Other",6242.34]
[null,"2020-05-17","Kiki Sho","Thriftway",20,"Fruit",499]
[null,"2021-05-19","Janis Figueroa","Safeway",50,"Dairy",898.13]
[null,"2020-11-13","Kiki Sho","Thriftway",525,"Vegetables",7229.91]
[null,"2021-06-02","Chantel Mims","Walmart",450,"Vegetables",6973.31]
[null,"2020-04-15","Chantel Mims","Lucky's",1,"Fruit",25.95]
[null,"2022-02-08","Chantel Mims","PCC",46,"Other",1405.65]
[null,"2020-11-15","Hien Pham","Target",2,"Other",49.9]
[null,"2022-01-09","Hien Pham","Target",175,"Dairy",3546.38]
[null,"2020-09-08","Chantel Mims","Thriftway",550,"Fruit",8839.19]
[null,"2022-03-07","Chantel Mims","Thriftway",500,"Other",6885.63]
[null,"2021-07-06","Tynia Malone","Target",350,"Dairy",5624.94]
[null,"2022-07-18","Kiki Sho","Target",10,"Other",269.5]
[null,"2021-05-06","Chantel Mims","Safeway",525,"Fruit",10550.53]
[null,"2021-05-23","Kiki Sho","Lucky's",8,"Fruit",287.60000000000002]
[null,"2020-11-23","Hien Pham","Target",35,"Fruit",1299.78]
[null,"2020-03-24","Tynia Malone","Target",300,"Dairy",7364.03]
[null,"2021-07-11","Chantel Mims","Thriftway",-30,"Vegetables",-687.23]

{"sheet":"Last Date-Time for 911 Call","index":3,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}