The Truth about Cats and Dogs

Ever wish Excel could tell you a fun cat or dog fact every time you click refresh? Good news — it can! In this post, we’ll walk through a short, practical project that uses Power Query to pull random facts about cats and dogs from public APIs directly into your worksheet.

Whether you’re a pet lover or just curious about Power Query, this tutorial is a lighthearted way to learn a powerful tool that works just as well for business data as it does for fun facts.

Video

What You’ll Learn

By the end of this tutorial, you’ll know how to:

• Use Power Query’s “From Web” feature to pull data from an API
• Work with JSON responses inside Power Query
• Use the Drill Down feature to extract a specific value
• Refresh the query anytime to get a new fact
• Format cells for clean text display

All using built-in Excel tools — no VBA or add-ins required.

Step 1: Understand the Source — Public APIs for Cat and Dog Facts

We’re using two public URLs that return a random fact every time you visit:

• Cat facts: https://catfact.ninja/fact
• Dog facts: https://dogapi.dog/api/v2/facts?limit=1

When you open either of these links in a browser, they return something called a JSON string — a lightweight format often used for sharing data over the web.

Here’s an example of what the cat fact API returns:

{
  "fact": "Cats have five toes on their front paws but only four on the back ones.",
  "length": 72
}

Power Query is fantastic at working with JSON. So now, let’s get that data right into Excel.

Step 2: Import a Random Cat Fact with Power Query

To start, we’ll pull in a cat fact using Power Query.

1. Go to the Data tab
2. In the Get & Transform Data group, select From Web
3. Paste this URL:
   https://catfact.ninja/fact
4. Click OK

Power Query will connect to the API and recognize the JSON structure.

Transform the Data

Once loaded in the Power Query Editor:

• Rename the query to something friendly like Cat Fact
• You’ll see a single record with fields like fact and length
• Right-click on the fact field and choose Drill Down

This tells Power Query to return just that value.

Next:

• Go to Home > Close & Load To…
• Choose Table
• Select an existing worksheet and a cell where you want it to appear

Now you’ll see a random cat fact directly in your worksheet!

Step 3: Add a Random Dog Fact

Let’s repeat the process for dogs.

1. Go to Data > From Web
2. Paste in the dog fact API URL:
   https://dogapi.dog/api/v2/facts?limit=1
3. Click OK

This time, inside the Power Query editor, use the Open As > JSON command.

Once inside the editor:

• Drill into the facts list
• Click on the first record
• Then drill down again to get the actual text of the fact

Rename the query to Dog Fact, and again, Close & Load To a cell in your worksheet.

You’ll now have both cat and dog facts right in Excel!

Step 4: Refresh for a New Fact

Here’s the fun part — every time you want new facts, just:

• Go to Data > Refresh All

Both queries will re-run, and because the APIs return random facts on each call, you’ll get something different each time!

Optional: Format for Better Readability

To make the facts easier to read:

1. Right-click the cell with the fact
2. Choose Format Cells
3. Go to the Alignment tab
4. Check the box for Wrap Text

This keeps longer facts neatly inside the cell instead of spilling over.

Power Query Features Highlighted

Here’s a quick summary of the Power Query features we used:

• From Web: Connects Excel to a web API or online data source
• JSON Parsing: Automatically recognizes and navigates structured web responses
• Drill Down: Focuses the query output on a single value or field
• Close & Load To…: Gives you control over where data appears in the workbook
• Refresh All: Re-runs the query and updates results automatically

Why This Matters (Even Beyond Pets)

While this example is light and fun, the takeaway is serious: Power Query can bring live data into Excel from just about anywhere.

Whether you’re pulling financials, sales data, or KPIs — or just impressing your coworkers with a new animal fact each morning — these same steps can be applied in countless scenarios.

Conclusion

This is one of those projects that makes learning Excel actually fun. You get to practice pulling in web data, working with JSON, drilling down, and loading it into a neat table — all skills you can apply to serious projects too.

Got questions or want to share your favorite cat or dog fact? Drop a comment below — I’d love to hear from you!

Happy querying!

Sample File

Want to try it out without building from scratch?

It includes everything set up and ready to go — just click Refresh All to get new facts.

FAQs

1. Do I need Excel 365 for this to work?
Nope! Power Query is available in Excel 2016 and later. Just look for the “Get & Transform Data” group on the Data tab.

2. Why is only one cat/dog fact returned?
That’s how these specific API endpoints are designed — they return a single random fact with each request. There are alternative endpoints that return multiple facts, just check out the documentation.

3. What is JSON and why does Power Query use it?
JSON (JavaScript Object Notation) is a common format for sending data online. Power Query is great at handling JSON, making it easy to extract exactly what you need.

4. Do these APIs cost anything?
Nope — both of the APIs illustrated offer free access, though they do have rate limits to prevent abuse.

5. How often can I refresh?
You can refresh a few times a day. If you exceed the rate limits, you’ll get an error and have to wait a bit before refreshing again.

6. Can I automate the refresh daily?
Yes! You can use Workbook Connections and Power Automate or scheduled tasks, but that’s a more advanced topic.

7. Is this secure?
Yes — you’re only pulling public data from trusted APIs. Power Query uses HTTPS for these requests.

8. What if I want a different animal?
Use your favorite ai assistant or search online for a public API for that animal and follow the same steps. Just be sure it returns data in a format Power Query can parse.