Excel: Merging Data with Power Query (Bonus Video!)

Power Query in Excel is an incredibly powerful tool. A while back, I posted a brief introduction to this feature. I wanted to expound upon that information with another great use for Power Query: merging data. This is a common situation: a lot of us have data files we are pulling from different sources that contain unique information. Combining this data into one powerhouse sheet can be a great time saver. Let’s take a look at how to do this.

Video

Hey, I am trying something different! Thank you to those of you who completed our ITS Applications Training Survey last week. A ton of you said you wanted videos and exercises, so let’s try it out today. Below is a link to a YouTube video, and read on for the exercise files. I will also include written instructions, as per usual.

Exercise Files

Here are a couple downloads for you:

MergeExercise: Start Here

MergeExerciseSolved: to check your work

Two Disclaimers

For this Byte, I am assuming you already have some basic knowledge about Excel, like Tables and PivotTables. If you would like to learn more about either of these, I would love for you to attend a session! Look for sessions in myTraining.
Also, you will likely need to be using the Windows version of Excel, preferably 365, though this should also be possible in 2016.

About This Workbook

This spreadsheet contains three sheets: each with unique information about our key players in the name column.

The first sheet contains their number and location,
The second contains their favorite animal, and
The third their favorite candy. Our goal is to bring all this data together in one powerhouse worksheet.

Import Data as Queries

1. In the first sheet, place your cursor in the middle of the data. Press Ctrl + T to turn it into a table. You can also do this from the dropdown in the Home tab.

2. Make sure “My table has headers” is checked and all the data is selected, and press OK. Repeat this step with each sheet of data, so you have a total of 3 tables in the worksheet.

3. Select one of the tables. Go to the Data tab in the ribbon, Get & Transform data group, and select From Table/Range. If you don’t see this button in the Get & Transform Data group, check the Get Data dropdown for it.

…you will be taken to the Power Query editor screen…

4. In Applied Steps on the right, click the X next to changed type to remove it.

5. In the upper left select the dropdown for Close & Load To…

6. Select Only Create Connection and press OK.

7. Repeat Steps 3 – 6 for all the tables you created. You will see a list of tables appear on the right side of the screen under Queries and Connections.

Merge the First Two Queries

1. In the Data tab, go to the Get Data dropdown, hover your mouse over Combine Queries, and select Merge.

2. In the Merge screen, select your first table in the upper dropdown, and your second table in the second dropdown.

3. Select the two common columns: in this case, it is the Name column. Click on the Name column under each table. Press OK.

4. In the Power Query screen, find the double headed arrow at the top of the last column, from the merged table. Click on this button to expand information.

5. Select what you would like to expand. In this case, we are unchecking the name box, and just keeping Animal selected. Press OK.

This will expand the Animal column from the second table. Note that it applies its own naming format to the column title. You can rename the column, if desired. Double click on the title to change the name.

Keep this Power Query screen open for the next step.

Merging Subsequent Queries

We now have 2 merged queries. We can add the last one from within the Power Query screen.

In the Power Query ribbon, Combine group, select Merge Queries.

2. In the Merge screen, your Merged query will appear at the top by default. In the second dropdown, select the final table. Just like you did in the previous section, select the Name columns in both queries, and press OK.

3. Just as you did in the previous section, the final table appears at the top with a double headed arrow. Click on the double headed arrow to expand. And leave only Candy selected. Press OK.

4. Select Close and Load To… but this time keep the default selected: Table on a New Worksheet. Press OK.

Final Table

Our merged query is visible as a table on a new worksheet.

A Couple Notes

It helps if you think about your final table like a Pivot Table:

If you need to change something about the data, be sure to change the data source (one of the data sheets), not the merged query.
When you do change the data source, be sure to refresh the query. A simple way to do this is to right click on the merged table and select Refresh.

Thoughts?

What do you think? Did you like the video? I hope this was helpful. Feel free to send me an email anytime.

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

La’Rell Marion (picture coming soon!)

Top 10 Secret Key Commands in Microsoft Office

Whenever I mention one of these shortcuts in a session, I see people taking out their pencils to write them down. Most of us know some commonly used keyboard commands in Microsoft Office; Ctrl + Z to undo, Ctrl + C to copy, and so on… but I have some lesser-known favorites to share with you today. Also, take a moment to check out this article, which contains some more general shortcuts.

F12 = Save As

If you have taken any of my Essentials sessions, you know this one, one of my absolute favorites. Remember in the old days when you used to be able to select Save As from the menu, and you would be taken directly to a dialog box with everything you wanted in one place? If you are like me, you found that box to be way faster than clicking through the options one by one in the new File/Backstage menu.

So just press F12. It does everything you want, and works in all your favorite Microsoft Office programs.

F7 = Spell Check

Want a quick spell check? No need to visit the ribbon, just press F7.

Especially useful in Excel, where we don’t get the red squiggly lines underneath misspelled words like we do in Word.

F7 will also work in PowerPoint…

Alt + Q = Find Features

This is a big one. Sometimes you know that a feature exists in Microsoft Office, but you are just not sure where to find it. Press Alt + Q to search for it.

Forget how to get to the Accessibility Checker? Press Alt + Q and search for it…

While I still think it is a good idea to know where features live in the ribbon, this is an excellent tool to find what you are looking for in a hurry.

Ctrl + Y = Redo

Companion to the famous Ctrl + Z (undo), Ctrl + Y gets a lot less fanfare, and I have no idea why. It is a great cheat if you don’t know a key command for something. Ctrl +Y will simply recreate the last action.

Let’s say you want to insert a row into Excel in a variety of different places. Maybe you don’t know the key command (Shift Ctrl + +), so you insert one row… with the old-fashioned right click , Insert.

To insert another row, go to the new location, and hit Ctrl +Y. Click on the next location, Ctrl +Y, and so on. This will be much faster than performing the old “Right click, Insert” method 20 or so times.

It also works for formatting. A couple weeks ago, I was editing a Word document with quite a few pictures that I wanted to have identical style formatting. I selected one picture, applied a style format, then selected the remaining pictures, pressed Ctrl + Y and voila! All the same formatting was applied.

Note: Ctrl + Y will only apply the last action, so if you were making several format changes to an item, you might be better off copying formatting with the Format Painter. Please attend an Excel, Word, or OneNote Essentials session to learn more about this amazing feature!

Ctrl + Home/Ctrl +End = Go to the Beginning or End

Need to get back to the top of a document, or conversely, need to find the end? Try Ctrl + Home or Ctrl + End to navigate.

Ctrl + Home will take you back to the top of your document, and Ctrl + End will take you to the bottom.

Note: this will require that you are using a full keyboard… sometimes the Home and End buttons are not present on smaller keyboards or laptops.

Ctrl + Shift+ (arrow) = select an entire column or row

If you have attended Excel Advanced Formulas you know this one. Ctrl + Shift + Down Arrow will select all the data in a column. This also works with a right arrow to select all the data in a row. And in Word, it will select an entire row of data at a time.

Outlook: Ctrl + Shift + M = Create Email

I mentioned in a previous article that Outlook has some amazing time saving shortcuts. This one and the next one are my two favorites.

Regardless of the Outlook module you are working in, Ctrl + Shift + M will create a new email message. No need to return to the mail module.

Below I was in my Calendar module, and started an email with this command.

Outlook: Alt + S = Send Email

Once you are done typing your email, don’t touch that mouse… Alt + S will send the email.

There have been occasions where someone was standing behind me as I used this command and the last in Outlook, and they exclaim, “How did you do that??” The wizardry of key commands!

Word: Alt + Ctrl + 1 (or 2, or 3) = Format as Heading

We are all utilizing headers after attending Word Essentials, right? Well, here is a quick way to set a line to Heading 1, 2 or 3: Alt + Ctrl + 1 (or 2, or 3).

Excel: Ctrl + T = Convert Range to Table

After you attended Excel Essentials, you ran back to your computer to convert all your ranges to tables, right? The functionality we see for tables goes even beyond what we see in Essentials and Pivot Tables sessions. It is just a good practice to start utilizing them.

Instead of using the Table feature from the Home tab of the ribbon, place your cursor in the middle of your data and press: Ctrl + T.

Of course, you can still make adjustments via the contextual Table Tool tab.

Thoughts?

What do you think, are any of these one of your favorite shortcuts? Or do you have a different favorite secret shortcut to share?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Kaleb Basham
Joan Wilson

PowerPoint: New and Improved Slide Show Recording

If you haven’t visited PowerPoint’s recording options for a while, you are missing out. The Record Slide Show feature is typically utilized when your end goal is to export your presentation to a video format; either to be posted online somewhere or in a kiosk setting. Previously Record Slide Show would allow you to record audio to go along with your presentation. Then you could export a complete video showing your slides with your voiceover. This was already a great tool, but the feature received a fantastic facelift in 365, enabling even more abilities. Let’s take a look.

Record Slide Show

Record Slide Show lives on the Slide Show tab, Set Up group.

You have the choice to start from the selected slide, or from the beginning of the show.

Control Center

The new control center for recording a slide show is phenomenal. Within this screen are the following tools:

1. Start or Stop Recording

In the upper left part of the screen is the ability to start or stop recording. Once you create a recording, there will also be the ability here to replay what you have created.

2. Notes

In the center of the screen is a way to access any notes that are tied to a slide. Click on the dropdown to view them.

To the right of the Notes dropdown is the ability to make the text of the notes larger or smaller, signified by a large and small “A”.

If you would like to learn how to create Notes, please attend a PowerPoint Essentials session!

3. Incorporate Front Facing Camera to Record Yourself

The exciting aspect of the improved feature is that it will incorporate your computer’s camera to allow you to record audio and video of yourself within the recording. Your video will appear in the lower right of the PowerPoint presentation.

There is the ability to turn off audio or video below the image.

Note: if you do not have a camera or microphone, the options will be greyed out. You will still be able to record a slide show, however, and can incorporate things like markup (below).

4. Markup

In the bottom center of the screen are some markup tools. As with all markup tools, this will work best with a stylus, if possible.

The recording will incorporate your markup as you create it.

5. Slide Number and Timer

Lastly, in the lower left you will see slide number and a timer.

A Couple Notes

Shortcuts

Most of the shortcuts in the Record Slide Show view will be similar to those you have been using in Slide Show view:

To advance to the next slide, you can use your usual method: the space bar, an arrow key, a clicker, etc.
To escape from this view, click your Esc key.
Caution: some shortcuts, like W for whiteboard, or B for a black screen, do not work in this view.

Export Options

Don’t forget, you can export your final document via your usual method,

1. File –> Export

2. Select a method. In the case below, I selected Create a Video.

3. Be sure you have selected Use Recorded Timings and Narrations to incorporate your recording.

**Important: if you receive an error when you try to open the video, give it a little time. Often the icon for the exported video appears before the video has finished exporting. Give it a few minutes and you should be good to go.

Thoughts?

Did you know about this improved feature? What do you think?

Congratulations, Power Users!

Congratulations to our newest Power Users! For the full gallery, and more information about the WSU Microsoft Office Power User Program, please visit: wichita.edu/poweruser

Michelle Dehaven