Speed Challenge Day 5

Description

This is where it all comes together! In this video, we'll use Power Query to retrieve and prepare data, Tables to store the data, and a PivotTable to summarize the data. It has all been leading up to this 🙂

Moss Creates One-Click Report Refresh

When we combine the tools we learned during the Challenge, including Tables, Power Query, and PivotTables, we are able to create workbooks that can be easily refreshed. My student Moss combined these tools and others he learned into his operational reports. He said "Now, each month when we have new data available, I simply hit refresh and it performs all those steps for me." See what he did here.

File

If you'd like to give it a try after watching the video, download the Excel file ChallengeConclusion.xlsx and the related data file ChallengeData1.csv.

Try It

Exercise 1

• Data > Get Data > From File > From Text/CSV.
  • Browse to ChallengeData1.csv
  • Click Transform Data
• We have several options for getting the department name from the Dept column. One option is:
  • Select the Dept column.
  • Transform > Extract > Text After Delimiter.
  • In the Delimiter field, enter a colon (:).
  • Click OK.
• One option for getting the account name from the Acct column:
  • Select the Acct column.
  • Transform > Extract > Text After Delimiter.
  • Enter a colon and click OK.
• Send the data back to Excel.
  • Home > Close & Load > Close & Load To ... Table an an Existing worksheet.
  • Select the Data worksheet.

Exercise 2

• Create the PivotTable.
  • Select any cell in the data table (on the Data worksheet).
  • Insert > PivotTable.
  • In the resulting dialog, select Existing worksheet and browse to the Report worksheet. Select a cell on the worksheet that represents the upper-left corner of the report.
• Define the structure of the PivotTable report.
  • Drag the Account field into the Rows layout area.
  • Drag the Amount field into the Values layout area.
  • Drag the Period field into the Columns layout area.
• Format the values by right-clicking any numeric value in the PivotTable and selecting Number format.
  • Select any desired format.

Exercise 3

• Note the total of the PivotTable
• Navigate to the Data Table and add a Total Row.
  • Select any cell in the Table.
  • Table > Total Row.
• The Table total should agree to the PivotTable, confirming all data made it into the report.
• Yay ... you did it 🙂