Excel: Power Query: A Simple Introduction

We all occasionally find ourselves navigating the rough terrain of ugly data. Power Query is an excellent tool to have in your back pocket for this eventuality. But here is the deal, Power Query is just that… powerful, and because of this, potentially complicated. In fact, I know a lot of people who have been interested in learning about this beast, only to get quickly overwhelmed in the complexities. So here is my aim with this Byte: a simple introduction to Power Query. Let’s check it out.

Power Query editor screen

What is Power Query?

Power Query is a feature in the PC version of Excel 2016 or later, also known as Get & Transform. There are a multitude of uses for this feature, but it really shines to take on ugly data like this:

Ugly data: lots of information crowded into one column

… and transform it into something more workable.

data neatly formatted as a table

Furthermore, since the result is a query that is connected to your data, it can be updated with a simple click of a button.

Exercise File

If you would like to follow along with my steps below, here is an Exercise File:

A Couple Notes:

  • You will need the PC desktop version of Excel 2016, 365, or 2019 to use this feature.
  • For this Byte I am assuming you have worked with Tables and Pivot Tables in Excel before. If you haven’t, please come to my Excel Essentials and Excel Pivot Tables Sessions and learn about them!
  • Needless to say, this is all fictional data.

The Scenario

The scenario is we have some ugly data that we have exported from a different source. The source has thrown lots of data into one column of a spreadsheet. Monthly, we are asked to create a pivot table of fees owed by class and area code, but this will be rough going, given how the data looks:

Unformatted data

Format as a Table

I have mentioned before that formatting your data as a table has many advantages, and here is another excellent example. There are many data sources you can use for Power Query, but let’s start here for now.

1. Click on any cell inside the data. Do not preselect the entire column.

2. In the ribbon, select Format as Table, and select any style.

Format as table dropdown selected

3. Make sure that all the data is encompassed in your range, make sure My table has headers is checked, and press OK.

Create Table dialogue box, my table has headers selected.

More details about tables are discussed in the Excel Essentials training, so check out one of those sessions if you have not already.

Same data, formatted as a table.

Create a Query

1. Go to the Data tab, and in the Get & Transform Data group, select From Table/Range.

(note: you can also access this from the Get Data dropdown if it is not readily visible)

Data table, Get and transform data group, from table/ range.

2. You will be taken to the Power Query Editor.

Power Query screen

This is a little like an alien abduction from your comfortable Excel home… the look and feel are kind of the same as the rest of Excel, but also kind of different. Power Query uses a different type of code than the rest of Excel, so some things will not be intuitive. I don’t want to get too far in the weeds, but for now note that:

  • There is no undo, but you can always delete a step on the right,under Applied Steps, where coincidentally, every step of the query will appear forevermore.
  • There is a ribbon with Transform and Add Column option. Although options will look similar on both tabs, items on the Transform tab will change an existing column, and items on the Add Column tabwill create a new one
  • A lot of options are also accessible on a right click menu.

Transform the Data

In the query, there is only one column with a lot of ugly data, but I notice most of the data is separated by a colon “:”. Let’s begin by splitting up the columns by this delimiter.

1. Click at the top of the column to select all of the data in the column.

2. In the ribbon, or on a right click menu, select Split Column –> By Delimiter.

Split column, by delimiter circled

3. Make sure Colon is selected, and Each ocurrence of the delimiter is selected. Press OK.

Split column by colon, at each occurrence of the delimiter selected.

Your data should split into separate columns at each occurrence of a colon.

Same data split into separate columns in Power Query screen

4. Let’s work on splitting the phone number. Select the phone number column, and Split Column –> By Number of Characters.

Split column, bu number of characters

5. We want to split after 3 characters, Once, as far left as possible. Click OK.

Split at 3 characters, once, as far left as possible.

Cool, now we have a separate column for the area code!

6. At the top of each column, double click on the title and give each column a name: Name, Area, Phone, Grade, Class, Fees.

Titles typed for name, area, phone

Load the Transformed Data

1. You have the option to name your query on the right side of the screen, under Query Settings –> Properties. If you will be doing multiple queries, this might not be a bad idea.

Query Properties, Name of Students entered.

2. After this, the final step is to press the Close & Load button in the Home tab.

Close & Load circled in home tab.

Like magic! Our data loaded to a new tab in a new table.

Old data organized into a table.

Good News Part One: Data is Easier to Work With

Okay, this data is going to be so much easier to work with. Remember, my original goal was to make a pivot table showing fees owed by area code. Now that this data is in a table, in a few clicks, I have exactly the information I need. Beautiful!

Pivot table by area code and class, calculating fees.

Good News Part Two: Updating Data

We could have fixed up that data in a variety of other ways in Excel (hello, Flash Fill!), but the especially cool part about Power Query is the ability to refresh data in the future with one click. Remember I mentioned this was a monthly report I had to create… so here is what I would do next month… rather than recreate all the steps to make the data readable again:

1. Load the new data into the source table.

2. Right click on the query table and select Refresh

All the query steps we did in the previous section will happen automatically with my new data.

Right click menu, Refresh circled.

More Types of Queries

This example involved a query from a table within a document, but you can query tons of different sources: Excel documents in seperate locations, Access databases, Azure databases, online sources… seriously, check out all the options on the Data tab, Get Data dropdown.

Get data dropdown with location options

Interested in a Power Query Session?

This was a very basic introduction to Power Query. There is a lot more to cover about this amazing feature. WSU friends, I would like to poll the audience here… I think it would be fun to offer a Power Query Session to delve more deeply into its various abilities, but would like to hear from you all if this is something that would interest you. If you are interested, please send me an email!

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

Christina Covey

Christina Covey

Brandon Whiteside

5 Tips for Optimizing Charts

Charts can be incredibly challenging. They represent where the right brain and left brain meet… where computations and numbers collide with art and color. To be able to convey true meaning with graphics is a very special skill; here are 5 tips to help you along the way.

Line chart showing two years of chocolate pie revenue

1. Determine Your Message

Two important questions to ask yourself before you dive in to chart creation:

  • Who is your audience?
  • What is your message for them?

Cognitive Overload: Be Kind to Your Audience

One mistake a lot of us make is overloading the audience with too much information. This concept is called cognitive overload. Sometimes we zoom out in an attempt to show lots of data… and end up burying the story we are trying to tell in the process. The audience loses patience, and the entire message is lost.

Horribly messy pie chart titled Star Trek Fans

Consider this unfortunate pie chart… what story was the creator trying to tell? We are going to find some better solutions for this data shortly.

Audience Motivations

Some other important questions to ask are:

  • What is important to your audience?
  • What motivates them?
  • What is their level of knowledge on the topic?

All of these questions should influence the way you determine your message. We are going to revisit this first point frequently throughout this article. Even though it seems like the simplest of concepts, it is often the most forgotten.

2. Select the Right Chart Type

If the picture above gave you minor palpitations, part of the problem may be that someone picked the wrong chart to convey this information. There was a lot of data to comprehend here, more than a pie chart could feasibly tolerate. The chart below uses the exact same data set formatted as a column chart. Is this easier to understand?

Column chart showing star trek fans by state

Alright, the message is still pretty unclear, but we are moving in the right direction.

Here are a few general rules for selecting chart types:

Pie Charts

  • Pie charts should contain no more than 5 pieces, and preferably fewer than that.
  • Pie charts are supposed to show portions of a whole, so the whole should ideally be represented, even if you incorporate grouping (discussed next).
  • Remember your message and your audience (Tip #1), and then consider: are all your pie pieces all the same size? If so, is this pie chart really showing what you want to tell? Maybe it is… maybe your story is that all things are equally represented. But if that is not your message, consider a bar or column chart.
  • Consider incorporating data labels and callouts for further clarification.
Pie Chart titled Favorite Weekend day, showing 86% Saturday and 14% Sunday (fictional data).

Bar Charts and Column Charts

Bar and Column Charts are a great option if you have more information to display.

  • The main difference between bar charts and column charts is that bar charts are composed of horizontal data bars and column charts are composed of vertical data bars.
  • Both are great options for showing larger numbers of data sets.
  • Still, beware of cognitive overload with too much information (see Narrow your Focus for more tips below).
  • Clustered column charts are great for showing quick comparisons between small groups.
Clustered column chart showing dessert sales by three people.

Line Charts

  • Line charts do well comparing two competing data sets over a time period, like monthly sales figures this year stacked up against sales figures last year.
  • They also can help a viewer quickly ascertain overall trends at a glance.
  • One tip: when possible, start the Y axis at 0. This should already be the default setting.
Chocolate pie revenue line chart showing two years of data.

So Many More…

There are so many more charts to choose from! Check out this helpful Chart Chooser for more assistance with selecting the right type of chart.

3. Narrow Your Focus

For this section, I would like to revisit our original problem chart from the beginning. Remember, we started with this:

Horribly messy pie chart titled Star Trek Fans

And changed the chart type to a column chart:

Column chart showing star trek fans by state

…but this is still not an ideal situation.

What story do you want to tell?

Tip #1 will help you narrow your focus for this step. In this case, we had a (fictional) chart of total U.S. Star Trek fans broken down by state. Let’s explore a few stories you may want to tell with this data.

Avoid displaying too much information

Because all the states are represented, this means there are 50 data points. Do we really need to show all of this information? Perhaps, you decide that the story you want to tell is to show the states with the highest rates of Star Trek Fans.

How about removing the states that are not pertinent to your story? Let’s try to narrow our focus by filtering out some unnecessary information (i.e. the lower figures in the data set) with the filter button to the right of the chart.

Filter dropdown next to chart

This leaves us with a more manageable data set, down from 50 points to 7…. let’s keep going.

Column chart titled states with the most star trek fans, showing 7 states.

Highlight Important Information

Another useful technique to drive home a point is to use contrast to highlight important information, visually pulling it to the foreground. Select any data bar, right click, and select Fill to choose any color in the color wheel.

Right click menu, fill selected.

Let’s make use of this opportunity to pull the highest states to the foreground with a deep color, and grey out the others, pushing them to the background.

Take a look at the same filtered graph, recolored. Maybe the story you want to tell is that Hawaii, Kansas and New York had the highest populations of Star Trek fans… if so, this could be a cool way to do it.

Column chart for states with the most star trek fans, with highest figures in a darker red shade.

Group information together

We initially started with a pie chart, and there still might be a case for this type of chart with this data. Grouping together pieces of information is another great way to focus in on your overall message.

Perhaps your goal is to spotlight Kansas, and tell the audience that Kansas contains 5% of the country’s Star Trek Fans. Why not group together the states that are less relevant? We can also incorporate the previous highlighting technique.

This is much easier on the eyes than the original pie chart.

Star trek fans by state, showing Kansas pulled out on its own and the other states grouped together.

Do you see why asking yourself the questions in Tip #1 can put you on the right track for creating a meaningful chart?

4. Choose Words Carefully

Take a look at the chart below. ..

Very wordy pie chart

How long did it take you to grasp the meaning of this chart?

Wherever possible, keep the words to a minimum. And when in doubt, refer back to Tip #1 and ask yourself:

  • Who is your audience?
  • What is your message?
  • How much time would you like them to look at your chart, and what would make the biggest impact?
simpler pie chart: people who like pie.

Remember, the point of a chart is to create a visual illustration of data. More words means less visual impact…

5. Don’t Forget About Your Colorblind Friends

Hey, don’t forget about your colorblind friends!

  • Approximately 8% of the male population and 0.5% of the female population is colorblind or color deficient. This means if 1000 people will be viewing your chart, about 45 of them may not be able to differentiate between certain colors.
  • You can still use color in your charts… Just remember that you don’t want to use color alone to convey meaning.
  • This is something we have talked about before, so if you would like to read more information about the use of color in documents, check out my previous article on the subject.

Just remember, this chart may look perfectly clear to you:

Student Satisfaction pie chart, inaccessible colors.

… but here is how it looks to someone with the most common type of color blindness:

Student Satisfaction pie chart from before, run through a color blindness filter, and the pieces are indistinguishable.

More Notes and Disclaimers

You probably know this stuff, but just in case…

  • You have seen example charts throughout this article. All the data used to create the charts is entirely fictional.
  • Do you want to learn more about how to make charts? Please attend one of my Excel: Pivot Tables, Charts and Pictures sessions listed in myTraining.

Thoughts?

Okay, what do you think Power Users? Do you think you will be able to put some of these tips to use with your data? I would love to hear from you!

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

Tera Park

Tera Park

Mary Ann Hollander

Mary Ann Hollander

John Keckeisen

John Keckeisen

Amanda Conner

Amanda Conner

Excel: Formatting an Entire Row Based on One Value

Have you ever gone through a list row by row to highlight rows based on one value in the row? Here is one example: you have a list of students, and you want to highlight their entire entry if they have an “A” in your class… so you hand-select an entire row, fill in the desired color, and repeat this process 100 or so times per semester. Never do that again! Let’s check out another versatility of Conditional Formatting: the ability to highlight an entire row based on one value in a specific column.

Before we jump in… big thank you to Samantha and Amanda for inspiring this Byte with this excellent question in (two different) Excel Advanced Formulas sessions!

Exercises

If you would like to follow along, here is a fictional class list to work with. Our goal with this exercise is to make an entire row yellow if a student has an A in the class.

A Word About Conditional Formatting

In my Advanced Formulas training we touch on formatting specific cells based on their value with Conditional Formatting, but this will be a bit different… in this case, we would like the entire row to be highlighted based on the value of one cell in the row (the grade).

If you haven’t already, I hope you will consider checking out an Excel Advanced Formulas session… for one, we cover Conditional Formatting in more detail, and for two we talk about concepts like absolute references, which I reference later in this Byte.

Create a New Rule: Crafting a Formula

1. Start by selecting all the data you would like the formatting to apply to: A2 through H77.

2. In the center of the Home tab, select Conditional Formatting, New Rule.

3. In the popup screen, select Use a formula to determine which cells to format.

4. Click into the box underneath the text Format values where this formula is true.

  • In your data, click on the cell with the first grade, in H2.
  • Excel will bring in its own language for this cell “$H$2”.
  • Type an equals sign ( = ) and (with quotes) “A”.
  • Your formula at this point will look like : =$H$2=”A”

Sidebar: We hit this in more depth in Excel Advanced, but as a reminder…

  • The “$” means an absolute reference, so a stagnant location in Excel. We see an example of this in class when we try to use autofill to carry down a formula.
  • The A has to have “” around it, since it is text.
  • If you want to learn more about these two points, please come to one of my Excel Advanced Formulas trainings.

Okay, we are not done with the formula yet… if we leave the formula like this, it will highlight the entire selection range (A2 through H77) if H2 has an “A” in it… this is not what we had in mind. To correct this, we are going to remove one dollar sign…

5. Since we realized that the row should not remain absolute, let’s delete one dollar sign (absolute reference). We are going to delete the dollar sign in front of the number 2. This will tell Excel that the row is relative; but the column will remain absolute.

  • Here is what your formula should look like now:

=$H2=”A”

We are not done yet, though… we still have to set up formatting…

Creating a New Rule: Formatting

It is easy to get so caught up in the formula that you forget to set a formatting rule. Right now the formula looks great, but Excel won’t make it look any different until we pick some formatting.

1. Select the Format button in the New Formatting Rule popup underneath where you placed your formula.

2. In the popup that appears, select the Fill tab. Select a color, then press OK.

3. Alright, this looks pretty good! We have a formula and a formatting. Press OK to see the results.

If all goes well, you should have something like this.

To sum up,Excel is looking through column H (which remained absolute with the “$”) for an “A”, and if it finds an “A” there, the entire row is highlighted all the way down (because the “2” was not absolute, thanks to removing the “$”)

Managing Conditional Formatting Rules

A few more things that might be helpful:

  • If you want to see the conditional formatting rules you have created, click on the Conditional Formatting dropdown, and select Manage Rules.
  • The default view shows you the rules present for any cells selected. So if you would like to see all the rules for a sheet, you can alter the Show formatting rules for dropdown accordingly.
  • Here is where you can also make adjustments to your rules: edit, delete, alter the cells it applies to (helpful if you selected too many or too few cells to begin with).

Thoughts?

What do you think, will you try out this type of conditional formatting in your documents? I would love to hear how you use this!

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

  • Kaylee Nungesser
  • Andrea Glessner