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!
6/14/2017 03:55:29 am
Does this only work if you have a paid subscription to Ancestry? I have online access to the Ancestry Library Edition via my university. When I try to download some census info, it comes up blank. When look at the tables it appears that Ancestry wanted a log in and password - which I do have to use to get access via the university library website. So it seems this is not going to work for me, at least with Ancestry. I will try some other sites tomorrow.
My mother-in-law's Roddys hail from Ballaghaderreen area inCounty Roscommon. If you find yours come from there, I have a few collected. Wondering how different using Power Query would be from just copy-and-paste but I imagine it would be a lot more efficient. Thanks for heads up. Maggie
11/19/2017 04:06:36 pm
you can do incredible things with Power Query. I have "automated" download of multipage results and extract active URLs from the results
7/20/2018 07:16:17 am
I have windows 10 with Excel 2016. The Tool is there. But it throws back an exception, complaining that all CR must be followed by a LF. This error should only occur if you are accessing the data from IIS, not a web host. Wondering if anyone has updated instructions or a fix for this??
8/5/2018 10:27:05 am
Excel 2011 and Excel 2016 for Mac
4/28/2020 05:20:24 pm
I have windows 10 with Excel 365. The Tool is there but I have two issues. First, it throws back an exception, complaining that all CR must be followed by a LF. Wondering if anyone has updated instructions or a fix for this? Secondly, do you have a solution that will automate download of multipage results and extract active URLs from the results? thanks
8/15/2021 05:54:04 am
This is interesting and I would like to try (I am on a Mac, so PowerQuery doesn't work). My issue, though, is doing the Ancestry search. I am looking for all residents in a town (Takoma Park MD) in 1910. When I do that search in Ancestry I get the PDFs of the census sheets, which I cannot import - I am not getting a list of data with a URL like you show. I must be doing the Ancestry search incorrectly. Can you explain how I can the kind of search result you show?
Your comment will be posted after it is approved.
Leave a Reply.
Mary Kircher Roddy is a genealogist, writer and lecturer, always looking for the story. Her blog is a combination of the stories she has found and the tools she used to find them.