MARY KIRCHER RODDY
  • Home
  • Coaching and Research
  • Lectures
    • Upcoming and Past Presentations
  • Searching For Stories blog
  • Publications
  • Contact
  • Resources
  • Privacy Policy

Searching for Stories

Power Query and Powerful Learning

6/13/2017

9 Comments

 
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.

Picture

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.  

Picture

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.  

Picture

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

Picture

​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!

Picture

​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!

9 Comments
Carmel link
6/13/2017 03:00:13 pm

Great tip, thank you!

Reply
John Heath
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.

Reply
Jill Ball link
6/15/2017 07:00:09 pm

Thanks Did this with Familysearch for my CurryAus Surname study. Will need to have narrower searches on Ancestry as ther are so many hits for Curry.

Reply
Maggie link
6/26/2017 03:59:53 am

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

Reply
gary haas
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

Reply
Joe Blackburn
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??

Reply
Steve Kunnmann
8/5/2018 10:27:05 am

Excel 2011 and Excel 2016 for Mac
Get & Transform (Power Query) is not supported on the Mac.

Bumer! Is there another solution?

Reply
Ed Harris
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

Reply
Jim Douglas
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?

Reply

Your comment will be posted after it is approved.


Leave a Reply.

    Author

    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.

    Read more of Mary's writings at "Adventures of A Broad Abroad" and at Letters from Limerick

    Archives

    April 2021
    January 2021
    November 2020
    August 2020
    July 2020
    February 2020
    January 2020
    November 2019
    August 2019
    July 2019
    June 2019
    May 2019
    April 2019
    March 2019
    February 2019
    October 2018
    September 2018
    August 2018
    July 2018
    February 2018
    January 2018
    September 2017
    August 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    November 2016
    October 2016
    September 2016
    August 2016
    July 2016
    June 2016
    May 2016
    April 2016
    March 2016
    February 2016
    January 2016

    Categories

    All
    Achard
    Ahern
    Aldrich
    Amador County Genealogy
    Ancestry.com
    Archives
    Blair County
    Bradley Family
    Brannack
    Brannock
    Brown
    Brown Family
    California Genealogy
    Cemetery
    Census
    Citations
    City Directories
    Clark County
    Death Records
    DNA Strategies
    Education
    Enslaved People Research
    FamilySearch
    Family Stories
    Fields Family
    Freuhauf
    Genealogy Conferences
    Genealogy Education
    German Research
    Germany
    Graham Family
    Grandparents
    Hardy
    Hartmann
    Indexes
    Ireland
    Kircher
    Letters
    Lunenburg
    Map
    Mapping Tools
    Midwest Resources
    Midwives
    Military
    Newspaper
    Newspapers
    New York
    Ohio
    Pennsylvania
    ProGen
    Railroad
    Records
    Research Techniques
    San Francisco
    Sonoma County Genealogy
    Spreadsheets
    Springer
    Tiburon
    Timelines
    Virginia
    Virginia Genealogy
    Vital Records
    War
    Webster
    World War II Research
    Writing

     Subscribe in a reader

    Enter your email address:

    Delivered by FeedBurner

    Picture
Powered by Create your own unique website with customizable templates.
  • Home
  • Coaching and Research
  • Lectures
    • Upcoming and Past Presentations
  • Searching For Stories blog
  • Publications
  • Contact
  • Resources
  • Privacy Policy