Speed Challenge Day 4

Power Query

Description

Power Query is a tool that helps us get and transform data. Get ... like we are retrieving data from another location. Transform ... like change ... we are cleaning it and preparing it for use.

Penny Cuts Time in Half

Power Query is only one of the tools that help automate tasks. There are others, and they can all help to save a ton of time. My student Penny prepares personal income tax returns for clients, which typically take about 20 hours. After implementing some of the things she learned, she can cut that time in half. Take a quick peek here.

To Open Power Query

  • Windows: Data > Get Data
  • Mac: Data > Get Data

File

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

Try It

Exercise 1

  • To import the data into Power Query, Data > Get Data > From Text/CSV.
  • Browse to the ChallengeData.csv file.
  • In the resulting dialog, click the Transform Data button.
  • Now that the data is in Power Query, it is time to clean it up by applying a few transformations in the next exercises.

Exercise 2

  • To split the Full Name column into separate First and Last name columns, begin by selecting the Full Name column.
  • Transform > Split Column > By Delimiter.
  • Select -Custom- from the delimiter drop down.
  • Enter a comma and space in the resulting field.
  • Click OK.
  • Rename the Full Name.1 column to Last by double-clicking the column header.
  • Rename the Full Name.2 column to First.

Exercise 3

  • To transform all State column values to upper case, select the State column.
    • Transform > Format > UPPERCASE.
  • To transform all Email column values to lower case, select the Email column.
    • Transform > Format > lowercase.

Exercise 4

  • To create a combined Address column, begin by selecting the Street, City, and State columns.
    • Note: hold down shift or ctrl on your keyboard to select multiple columns.
  • Transform > Merge Columns.
    • In the resulting dialog, select -Custom- from the Separator drop down.
    • Type a comma space in the resulting field.
    • Leave the new column name value as the default Merged.
    • Click OK.
  • Select the Merged column, hold down Ctrl, and select the Zip column.
  • Transform > Merge Columns.
    • In the resulting dialog, select Space from the Separator drop down.
    • Change the new column name to Full Address.
    • Click OK.

Exercise 5

  • To load the results back to an Excel Table, select Home > Close & Load > Close & Load To...
    • Note: select the bottom half of the Close & Load command button.
  • In the resulting dialog, select Table and Existing worksheet.
    • Select the cell that represents the upper left corner of the table.
    • Click OK.

Conclusion

Power Query is a tool that helps us get external data and transform it on its way into Excel.

Power Query is a really big place, so it will take time to explore all of the various transformations and capabilities.

As you may have suspected, we'll revisit Power Query in the next Challenge video 🙂