MANIPULATING
DATA WITH
POWER
QUERY
BY
JORDAN GOLDMEIER
L AB 1: M ERGI NG DATA .
You have been tasked to use Excel to manipulate data using Power Query
MODULE REQUIREMENTS
‣ Creating an Excel Table
‣ Opening Power Query
‣ Manipulating Data Power Query
‣ Closing and Loading To…
HINTS
Remember to play around with Power Queries different features.
STEP-BY-STEP INSTRUCTIONS
Click Steps Screen Shots
1. Open Practice
Data.xlsx
2. From on the Practice
Data tab, place the
selector anywhere
inside the contiguous
data region.
Click Steps Screen Shots
1. Go to Insert >> Table
or Press Ctrl + to
insert a Table.
2. Ensure My Table has
Headers is checked.
3. Ensure the data
region references is =
$A$1:$I$301.
4. Press OK.
5. Immediately after
creating your table,
change the Table
Name to Contacts.
You can edit a Table’s
name from on the
Table Design tab.
6. Pull the table into
Power Query. Do this
by going to Data >
Table/Range while
the Excel cursor is in
the Table.
Click Steps Screen Shots
7. Power Query will
load.
8. Once complete we
can begin making
edits to table.
9. We will first edit the
first_name column.
Double click into the
column so all the text
is highlight.
10. Replace this text with
First Name.
11. Do the same for
last_name. Change it
to Last Name.
Click Steps Screen Shots
12. Let’s filter out the
null values from City.
13. Click on the
dropdown arrow to
the right of the City
column.
14. Uncheck (null)
15. Then click the OK
button at the bottom
of the dropdown
popup.
16. Let’s merge the
columns Street
Number, Street
Name, and Street
Suffix into one
column called Street
Address.
17. While holding shift
click Street Number,
Street Name, and
Street Suffix
18. From on the
Transform tab select
Merge Columns.
Click Steps Screen Shots
19. In the Merge
Columns dialog box,
select Space as the
separator. Renamed
the new column to be
Street Address.
20. Click OK.
21. Let’s only show the
last for digits of the
social security
numbers provided.
22. Click on SSN column
header.
23. From the Transform
tab select Extract >
Last Characters.
24. In the Extract Last
Characters dialog box
set the count to 4.
25. Press OK to have it
take effect.
26. Let’s adjust the
telephone number
format to (###) ###-
####.
27. First select the
Telephone Number
column header.
28. From on the Add
Column ribbon, select
Column From
Examples > From
Selection
Click Steps Screen Shots
29. While referencing the
Telephone Number
rewrite the first two
entries in the correct
format.
Use the screenshot to
the right as a
reference.
The dark black font
represents the first
two entries I retyped.
The grey font reflects
what Excel learned
through my efforts.
Press OK.
30. We’re now finished
data wrangling.
31. Click File > Close &
Load To…
Click Steps Screen Shots
32. Select Table in the
top half. New
worksheet in the
bottom half.
33. Click OK.
34. Congratulations! You
have created a new
query with Power
Query.
35. Remember, you can
have the query
refresh any time by
pressing the Refresh
button from on the
Table Design tab.
