Guided Project 10-1

  1. Get data from a text file.
  2. Get data from an Excel workbook.
  3. Merge queries.
  4. Add a custom column in Power Query.
  5. Get data in Power Pivot from Microsoft Access.
  6. Create a measure and a KPI in a Power Pivot table.
  7. Use the data model to create a PivotTable.
  8. Create and format PivotTables.
  9. Build a dashboard.
  10. Create a new workbook and save it as [your initials] Excel 10-1.
  11. Get data from a text file.
    1. Click the From Text/CSV button [Data tab, Get & Transform Data group].
    2. Find and select txtWearEver-10.txt from your student data files and click Import. The Navigator displays the file name and previews the data.
    3. Click Transform Data to launch Power Query. The query displays all columns. Handbag products display “null” in the Size column.
    4. Click the Close & Load button [Home tab, Close group]. In the Excel table “null” displays as a blank cell.
    5. Name the sheet txtWearEver-10 if necessary. (The sheet is named automatically in subscription versions of Excel.)
  12. Get data from an Excel workbook.
    1. Click the Get Data button [Data tab, Get & Transform Data group], choose From File, and From Workbook.
    2. Find and select the WearEver-10 workbook from your student data files and click Import. The Navigator displays the sheet name and the table name.
    3. Click Current Stock. The preview includes two label rows at the top, and the icon replicates a worksheet tab.
    4. Click Inventory. This is the named table in the workbook.
    5. Click Transform Data to launch Power Query. This adds the table to the data model. (Click Refresh if prompted.)
    6. Click the Close & Load button [Home tab, Close group].
    7. Name the sheet Inventory if necessary. The Queries & Connections pane shows both data source names.
  13. Merge queries into a new query.
    1. Point to the Inventory name in the Queries & Connections pane to display the pop-up window.
    2. Click Edit to return to Power Query.
    3. Expand the Navigator Pane if necessary to see both query names.
    4. Click the Merge Queries drop-down arrow [Home tab, Combine group] and select Merge Queries as New (Figure 10-66).
    5. Verify or select Inventory as the table name in the top entry box.
    6. Click the Product ID column.
    7. Click the drop-down list for the second entry box and choose txtWearEver-10.
    8. Select its Product ID column as the matching field. The text file is a list of orders.
    9. Click the drop-down list for Join Kind and choose Right Outer. A right outer join displays all the records from the orders list and matches each to a corresponding record in the inventory data.
    10. Click OK. The new query is named Merge1.
  14. Manipulate data in a query.
    1. Scroll the window to see the txtWearEver-10 column heading at the right.
    2. Click the Expand/Aggregate button for the heading, confirm that all field names are selected, that the Expand button is selected, and click OK (Figure 10-67).
    3. Select the Product ID, Product, Color, Size, and Retail fields that are prefaced with txtWearEver.
    4. Click the Remove Columns button [Home tab, Manage Columns group]. The Date and Sold columns remain.
  15. Add a custom column in a query.
    1. Click the Custom Column button [Add Column tab, General group].
    2. Type Revenue as the New column name.
    3. Click after the = sign in the Custom column formula box.
    4. Double-click Retail in the Available columns list, type * to multiply, double-click txtWearEver-10.Sold in the Available columns list, and click OK (Figure 10-68).
    5. Click the Close & Load button [Home tab, Close group]. The new sheet displays in the workbook.
    6. Name the sheet Merge1 if necessary.
  16. Build a PivotTable from the merged query.
    1. Select cell A2 on the Merge1 sheet and click the Summarize with PivotTable button [Table Design tab, Tools group].
    2. Verify that the range is Merge1 and that New Worksheet is selected.
    3. Select the Add this data to the Data Model box and click OK. A blank PivotTable layout displays.
    4. Display the PivotTable Fields pane if necessary and expand the Merge1 field list.
    5. Select the Product ID, Product, txtWearEver-10.Sold, and Revenue boxes in that order.
  17. Format a PivotTable.
    1. Select cell B3 and change the label to Pairs/Items Sold.
    2. Change the label in cell C3 to Revenue.
    3. Select cell C3 and click the Field Settings button [PivotTable Analyze tab, Active Field group].
    4. Format the values as Currency with two decimal places.
    5. Click the Report Layout button [Design tab, Layout group] and choose Show in Compact Form. (If your report is already in compact form, you will not see a change.)
    6. Place the Product field before the Product ID field in the Rows area (Figure 10-69).
    7. Click the PivotTable Name box [PivotTable Analyze tab, PivotTable group] and name the PivotTable as pvtRevenue.
    8. Name the worksheet as RevPivot.
  18. Get Microsoft Access data in a Power Pivot table.
    1. Click the Manage Data Model button [Data tab, Data Tools group] to start Power Pivot. (The ScreenTip for the button is Go to the Power Pivot Window. Click Enable if prompted to install the add-in). The Merge1 query displays in the data model.
    2. Click the From Database button [Home tab, Get External Data group] in the Power Pivot window and select From Access. The Table Import Wizard starts.
    3. Click Browse, find and select Database-10 from your student data files, and click Open. The database name and file path display.
    4. Click Next in the Table Import Wizard dialog box (Figure 10-70).
    5. Verify or choose the Select from a list of tables and views to choose the data to import button and click Next.
    6. Select the box for tblSurvey and click Finish. There are 36 records in tblSurvey.
    7. Click Close in the Success box. The table displays on its own tab and is added to the data model.
  19. Create a calculated column in Power Pivot.
    1. Click the first blank cell below the Add Column header.
    2. Type =( to start a formula and insert the left parenthesis in the Formula bar.
    3. Click the first value in the “Comfort” column to insert its reference in the formula.
    4. Type + for addition.
    5. Click the first value in the “Fit” column and type + for addition.
    6. Click the first value in the “Style” column.
    7. Type )/3 to insert the closing parenthesis, a division symbol, and “3” to calculate an average of the three characteristics. The DAX expression displays in the Formula bar (Figure 10-71).
    8. Press Enter to complete the column.
    9. Double-click the Calculated Column 1 header; it should be highlighted in blue.
    10. Type Average Rating and press Enter.
  20. Create a measure and a KPI in Power Pivot.
    1. Select the Average Rating column if necessary.
    2. Click the AutoSum drop-down arrow [Home tab, Calculations group] and choose Max. The measure appears below the “Average Rating” column and calculates the maximum average (9.666666…), which is the value in row 36.
    3. Scroll the window to see row 36 and point to the measure to see its value in the ScreenTip.
    4. Select the Maximum of Average Rating measure at the bottom of the column.
    5. Click the Create KPI button [Home tab, Calculations group].
    6. Click the Absolute value button and enter 10 in the box and press Tab. A value of 10 is the highest possible rating for WearEver products.
    7. Select the second icon set, three ellipses with an X, an exclamation point, and a check mark.
    8. Drag the high range marker to a value of 7.5 or type 7.5 in the entry box (Figure 10-72).
    9. Click OK. A KPI icon displays with the Maximum of Average Rating measure at the bottom of the column.
    10. Select the first cell in the “Order ID” column.
  21. Create a PivotTable from the data model.
    1. Click the PivotTable button [Home tab].
    2. Select New Worksheet and click OK. A blank PivotTable layout displays in the workbook.
    3. Display the PivotTable Fields pane if necessary.
    4. Expand the Inventory field list in the PivotTable Fields pane.
    5. Select the Retail box. A single result cell displays in the PivotTable.
  22. Create a relationship in a PivotTable.
    1. Expand the tblSurvey field list and select the Order ID box. The table expands but the data is inaccurate.
    2. Click the Auto-Detect button. No relationship is detected because the common field names (Product ID and Order ID) do not match (Figure 10-73).
    3. Select Manage Relationships in the message box and click New.
    4. Click the drop-down list for Table and choose Data Model Table: Inventory.
    5. Click the drop-down list for Column: (Foreign) and choose Product ID.
    6. Click the drop-down list for Related Table and choose Data Model Table: tblSurvey.
    7. Click the drop-down list for Related Column: (Primary) and choose Order ID. The field names are different but the data type and content matches (Figure 10-74).
    8. Click OK and then click Close. The Retail column refreshes and displays accurate data.
  23. Edit data in a PivotTable.
    1. Expand the tblSurvey list in the PivotTable Fields pane and add the Comfort, Fit, and Style fields to the PivotTable.
    2. Select cell C3 and change the heading to Retail.
    3. Edit each heading in cells D3, E3, and F3 to delete “Sum of.”
  24. Place a KPI in a PivotTable.
    1. Expand the tblSurvey field list in the PivotTable Fields pane if necessary.
    2. Click the Maximum of Average Rating KPI line to expand its options.
    3. Select the Status box to display the icons in the PivotTable. Note that the calculated field in Power Pivot named Average Rating is not displayed in the PivotTable; it is used to build the measure and its KPI. (If you see values instead of icons, deselect the Status box in the PivotTable Fields pane and select it again.)
    4. Edit the label in cell G3 to Our Measure.
    5. Select column G, apply Center alignment, and AutoFit the column.
  25. Format and filter the PivotTable.
    1. Click the Grand Totals button [Design tab, Layout group] and choose Off for Rows and Columns.
    2. Right-click Retail in cell C3 and select Value Field Settings.
    3. Format the values as Currency with two decimal positions.
    4. Adjust the zoom size or scroll the window so that you can see rows 40:50.
    5. Click the Row Labels drop-down list. Because handbag products were not included in the satisfaction survey, the data is aggregated or grouped as (blank) at the bottom of the column. (If you do not see Row Labels, click the Report Layout button [Design tab, Layout group] and choose Show in Compact Form.)
    6. Scroll to the bottom of the list, deselect the (blank) box, and click OK (Figure 10-75).
    7. Click the Select button [PivotTable Analyze tab, Actions group] and choose Entire PivotTable.
    8. Apply All Borders (Figure 10-76).
    9. Select a cell in the PivotTable and click the PivotTable Name box [PivotTable Analyze tab, PivotTable group].
    10. Name the table pvtKPI.
    11. Rename the worksheet as KPI_Pivot.
  26. Copy PivotTables to a dashboard sheet.
    1. Insert a new sheet and name it Dashboard.
    2. Select the RevPivot worksheet tab and click cell A3.
    3. Click the Select button [PivotTable Analyze tab, Actions group] and choose Entire PivotTable.
    4. Press Ctrl+C to copy and click the Dashboard sheet tab.
    5. Select cell B4 and press Ctrl+V.
    6. Select cell F4 and press Ctrl+V again.
    7. Select cell B4 and name the PivotTable as pvtRev.
    8. Select cell F4 and name the PivotTable as pvtTotals.
    9. Display the PivotTable Fields pane for pvtTotals if necessary.
    10. Expand the Merge1 field list and deselect the boxes for Product ID and txtWearEver-10.Sold.
  27. Format PivotTables on a dashboard.
    1. Apply White, PivotStyle Light 1 to pvtTotals.
    2. Apply All Borders to pvtTotals.
    3. Apply the same style and borders to pvtRev.
    4. AutoFit columns B:C.
    5. Click a cell in pvtRev. Do not show Grand Totals or Subtotals [Design tab, Layout group].
  28. Insert and connect a slicer on a dashboard.
    1. Set the width of column A to 30.
    2. Click cell B4 and click the Insert Slicer button [PivotTable Analyze tab, Filter group].
    3. Select the Product box in the Merge1 list and click OK.
    4. Position the slicer window to start in row 4 centered in the column as well as possible.
    5. Size the slicer window to display all items.
    6. Apply White, Slicer Style Dark 3.
    7. Click the Report Connections button [Slicer tab, Slicer group].
    8. Select the pvtTotals box and click OK.
    9. Click Chunky Heel Boots in the slicer window.
    10. Press Ctrl and click Monroe Tote in the slicer window.
  29. Complete labels for the dashboard.
    1. Select rows 1:2 and set the height to 40.
    2. Select cell A1 and type WearEver Accessories.
    3. Format the font as Calibri 36 pt. italic.
    4. Select cell A2 and type Last Period Sales Data.
    5. Format the font as Calibri 28 pt. italic.
    6. Insert a blank row at row 1 and a blank column at column A.
    7. Hide the Gridlines [View tab].
    8. Turn off the Formula Bar and the Headings displays.
  30. Save and close the workbook (Figure 10-77).
  1. Uninstall Power Pivot if instructed to do so.
    1. Select the Options command [File tab] and click Add-Ins in the left pane.
    2. Click the Manage drop-down list, choose COM Add-ins, and click Go.
    3. Deselect the Microsoft Power Pivot for Excel box and click OK.

Do you need help with this assignment or any other? We got you! Place your order and leave the rest to our experts.

Place Your Order

Quality Guaranteed

Any Deadline

No Plagiarism