Lisa R. Holliday on Creating a List of Unique Values with Power Query

My name is Lisa R. Holliday, and I am an Evaluation Associate with The Evaluation Group in Columbia, SC. I was recently cleaning survey data and preparing it to be uploaded into a database. The survey had been offered multiple times, and I wanted to track the responses of participants who had completed the survey each time it was offered. To start with, I needed to create a list of non-duplicated names. I would then be able to use this list to determine which participants had taken the survey each time it was offered.

Hot Tip: Power Query can remove duplicates from a list. Power Query is a handy add-in for Microsoft Excel that allowed me to generate my list quickly and easily. Power Query is a business intelligence tool that works with Excel 2010 and 2013. It is available for free download at the Microsoft website: http://www.microsoft.com/en-us/download/details.aspx?id=39379

Cool Tricks:

Step 1: I created a unique field in my survey data that would allow me to identify each person.  Just in case I had two people from the same site with the same name, I concatenated their name with their location and job title. The final cell looked like this:

I then created a master list of names (with duplicates) using cut and paste. Once this was done, I was ready to load my data in to Power Query.

Step 2: I selected to load my data “From Table” under the Power Query tab.

Step 3: Within the Power Query window, I selected the concatenated column, and then “Remove Duplicates.”

After my query ran, I selected “Close and Load.” Excel created a new table comprised of unique values only.

Hot Tip: Why not use “Remove Duplicates” from the Data Tab?

Any data you load into Power Query can be refreshed, and the query will automatically be re-executed. This feature is valuable if you plan to add more data to your original data set. In contrast, “Remove Duplicates” under the Data Tab does not have this option.

Hot Tip: Other Functionality. Power Query has a lot of other useful functionality and is worth exploring. It can easily import data from a variety of sources (including websites), un-pivot data, and split columns (such as First and Last name).

Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.