Taking the Next Step in Family Tree Analysis

Last week I showed you how to use a spreadsheet to gain insight into your family tree. If you're a Mac user, be sure to see the useful comments beneath that article from Mac user Mick.

In the article I challenged myself to tackle a bigger project. I wanted to use Microsoft Power BI Desktop software to further dissect the data and look for trends. I've always been a frustrated programmer, meaning I want to be good at it, but I get so frustrated! This time I got clear step-by-step Power BI instructions from Microsoft Copilot—what a lifesaver.

Working with the Data from Your Family Tree

The first step was to import my spreadsheet of everyone in my family tree into the program. The spreadsheet I exported from MyHeritage Family Tree Builder last week had problems. It wasn't possible to use that file because several rows didn't follow the format. So I exported my people from Family Tree Analyzer (old reliable). Once I imported the spreadsheet, I could see all the category names in Power BI Desktop. These include ID, LastName, FirstName, BirthDate, BirthLocation, etc.

How many ways can you think of to analyze your family tree?

Next, in Power BI Desktop's Report view, I created a table to hold all the data. I gave it some visual formatting to make it easier for me to understand:

• Bold column headers with a color background.
• Alternating white and light green rows like some ancient, pleated computer printout paper.

Note: I'm using the town of Colle Sannita in these examples because it's so complete in my family tree. About 95% of the available vital records are in my tree. The other 5% are records of people from out of town or who I can't identify.

Then I used the Filters column to the right of my table and added one data field: BirthLocation. I chose Advanced filtering, not Basic filtering. This let me filter down to any birth location that contains a certain town name, like Colle Sannita. When I click Apply filter, I can see that it's working. Hurray!

But I want to see how many people that filter includes. How many people in my family tree have I documented as being born in Colle Sannita?

I consulted Copilot to find out how I can do this. First I had to add a new function to my data fields to count the rows. The Copilot guidance used the generic title of RowCount for this. Next, to show the row count, I had to add a "card" to display it on. I did that and formatted it until I got what I wanted: a whole number with a comma to show thousands. My total count before applying a filter is 85,362 (bigger than it was last week). To make it more straightforward, I changed the function's name from RowCount to People. Now my card says 85,362 People.

Let's put it to use!

• When I filter BirthLocation to contain Colle Sannita, I have 25,120 People.
• When I filter BirthLocation to contain Baselice, I have 17,335 People.
• When I filter BirthLocation to contain New York, I have 902 People.
• When I filter BirthLocation to contain Bronx, I have 293 People.

I can do this with MarriageLocation and DeathLocation. I can do it for last names. I'll clear out my filter and drag LastName into the Filter section. I'll type my name, Iamarino, in the search box, and I see right there that I have 815 people with that last name. When I click Apply filter, the main screen shows the filtered list of people and the card says 815 People.

Getting More Specific

Now let's try two filters at once. I know there was an earthquake in Colle Sannita in 1805 that killed many people. I'm going to drag both DeathLocation and DeathDate into the Filters area. Using Advanced filtering, I'll choose:

• DeathLocations that contain Colle Sannita, and
• DeathDates that contain 1805.

Combining these filters leaves me with 54 people. I know the earthquake happened on 26 Jul 1805, thanks to Colle Sannita expert, Dr. Fabio Paolucci. I see people in this list who died well before the earthquake. I'm going to change my DeathDate filter to "contains 26 Jul 1805" or "contains 27 Jul 1805". This brings the list down to 28 people. (I can't add more than two dates, but a visual scan of the dates showed that all the victims seemed to die on these two dates.)

I created another filter to find years with very high or low death counts. I added a filter for DeathLocation that contains Colle Sannita, and one for DeathDate. I can keep changing the year and clicking Apply filter to see how many deaths there were. Here are a few results:

• 1810 had 113 deaths
• 1811 had 165 deaths
• 1812 had 145 deaths
• 1813 had 88 deaths
• 1860 had 126 deaths

I wanted to find a way to export or graph these numbers, but FRUSTRATION! My only choice would be to keep changing the filter and jot down the results in another spreadsheet. (Any programmers reading this are laughing their heads off.) I tried creating a dashboard that could show the results of various filters, but it didn't work. If I do make a separate spreadsheet, I can use Excel to turn the data into line graphs and bar charts. I have started doing this. See the image below.

It didn't take long to enter facts into a new spreadsheet to make these line graphs.

Going Deeper

Next let's try a 3-part filter. I'm wondering if the girl babies outnumbered the boy babies in my ancestral hometowns. I'll create a filter that contains a BirthLocation of Colle Sannita and a BirthDate of 1810. I'll include Gender, which shows me the Female/Male split without my having to touch it. Then I'll keep changing the year.

• In 1810 there were 199 births: 103 female and 96 male. Girls win.
• In 1820 there were 170 births: 79 female, 91 male. Boys win.
• In 1830 there were 177 births: 93 female, 84 male. Girls win.
• In 1840 there were 139 births: 71 female, 68 male. Girls win, but it's a close one.
• In 1850 there were 210 births: 114 female, 96 male. Girls win.
• In 1860 there were 206 births: 113 female, 93 male. Girls win.
• In 1870, 1880, 1890, 1900, and 1910 the boys win. Change in the water?

Now let's take a quick look at the marriages I've documented in Colle Sannita. There are two main churches, but the one in the heart of town is much older. I'll filter the MarriageLocation to those containing the older church: San Giorgio Martire. Wow! I've got 4,119 people who I know married there. I'll change the filter to show the location of the newer church in the area called Decorata. I've got 245 people who married there.

How are these both odd numbers? My guess is because of people who had more than one marriage. Only the preferred marriage appears in this database.

One more filter for the road. Italians have a tremendous reverence for the Virgin Mary, so they use the name Maria a LOT. Even the boys got Maria for a middle name. So, how many people in my family tree from Colle Sannita have a first name that contains Maria? 21,491 people!

I'd love to hear your suggestions for other ways to dissect the facts in my family tree.