Power Query and Powerful Learning

I love teaching!  It’s great to be able to share what I know, and a wonderful bonus when one of my students teaches me something. 

About a year ago I wrote a blog post, “Spreadsheet Magic - Importing Data from Ancestry.com.”  Importing data from a website into an Excel spreadsheet can give you the chance to play around with it, make notes, and manipulate the data to help to see patterns and find more about your ancestors.   I demonstrated this at a recent presentation I gave at King County Libraries, and Marvin, one of my students, taught me a new trick.  Seems my way of importing is “so 2016.”  There a new tool on the block, Power Query, that whittles the importing process down to one quick step.

Power Query is a Microsoft tool, included as part of Excel 2016 and available as a downloadable add-on for Excel 2010 and Excel 2013.  You can download it here:  
​
Once you install Power Query on your computer, it will show as a new tab on the Ribbon at the top of your Excel Window.  When you open that tab, you will see several icons.  In the left most section, “Get External Data,” there is an icon, “From Web” which will allow you to import data quickly from a web page.

I am curious why my husband’s Irish immigrant ancestors settled in Madison County, Ohio in the early 1850s.  If I research some of the early Irish settlers in that community, those who were born in Ireland and lived in Madison County by 1850, I may find one who was from the same place as Mark’s great-great grandfather, Bartley Roddy.  I’d like to do a search on Ancestry to find a list of these people, and then use a spreadsheet to track and study them and record my research notes. 
​
I did a search on Ancestry for everyone born in Ireland and living in Madison County, Ohio listed on the 1850 census.  

I could type all that data in, but it would take me quite a bit of time to record all 130 names.  But with Power Query, I can copy the URL from the top of the page and get a direct import of the first 50 names.  (For the remaining names, I can copy the URLs for the second and third pages of results, and repeat the process explained below) 

To do the import, open up the Power Query tab, click on the “From Web” icon, and a pop-up window appears with a box where you can paste in the URL you copied from your Ancestry search.  

​Click OK.  A new pop-up appears where you can click on “Table 0” and the “Load” icon at the bottom.

​And voila!!!! An Excel table I can sort, filter, and manipulate to my heart’s content with space to record my findings.  I’m going to crack those Roddy origins, yet!

​Have fun importing web searches of your own.  And thank you, Marvin, and all my other class participants who have taught this teacher such great stuff!