404 Status Checker Power Query

Problem we are solving:

While there are many tools that give 404, doing this in Bulk and in Excel where all our data would usually sit is painful. There are complex VBA scripts also available, but in just a simple Function we solve this problem that troubles many SEOs globally.

What you need?

  1. Excel 2013 or above
  2. In Excel 2016 Power Query comes pre-installed but for 2013 you can download Power Query from here.

Let’s get started

The first step is to write a Custom Function in Power Query that will go to any URL and check the Status. In this case we shall use the following Power Query functions.

For this we shall be using the following super simple Functions in Power Query

  1. Contents() – this pulls the Data from any URL. Think of this like look at the Page Source for any page.
  2. Metadata() – we use this to get the Meta Data information from the URL Header

NOTE – We use Excel 2013. For more recent versions, the screenshots might differ a little


In order to write a custom Function, follow these steps:

  1. Under the Power Query Tab click on From Other Sources and choose Blank Query. This is where we shall create our simple 404 Checker Function.
11
  1. This will open the Query Editor Screen. Rename the Query Name to fn404Checker. I would highly recommend using sensible function names, else with multiple functions one can easily get lost.
22
  1. After this go to the Advanced Editor section and Copy the below Code.

(urlToCheck as text)=>

let

    Source = Value.Metadata(Web.Contents(urlToCheck, [ManualStatusHandling={404}]))[Response.Status]

in

    Source


  1. Yes – its just one simple like of Code that gets the job done and this alone would same some poor soul in your company hours of effort.
  2. Once done, you will see an Input section where you can now test the function. Put any URL that you want to check the Status for. For example we shall put https://cashkaro.com. The output is 200 as you would expect.
33
  1. That’s it. Now we need to create a List for the 100s of URLs that we want to check the 404 status for. For this we first make a list and Load this List in Power Query. This we need to do with Power Query every time. To Load this this into Power Query, select the List, click on the Power Query tab and select ‘From Table/Range’
44
  1. This would automatically open the Query Editor screen. As before Rename the Query to something sensible like 404 Checker
55
  1. Now Under Add Column, go to Invoke Custom column and call the Function ‘fn404Checker’ we made above. And hit ok.
66
  1. That’s it. Now just go ahead and Load this Tables into a New Worksheet.
777
  1. And just like that you have the 404 Status for multiple pages in seconds. This can be used for 1000s of pages also and hence is a very powerful tool. Online sites only allow checking for 20-100 at a time, then we need to export back into excel. This is just easily done from Excel itself.
888

Simple Enhancements Possible

  1. We can put a filter to only have the 404 Pages. For this just add a filter in Step 9 for 404 only

If you are looking for sample Excel Sheet with the Code please do get in touch with rohan@cashkaro.com. Feel free to share this with others who can benefit from this.


Other Useful Blogs for SEO Made Easy with Power Query

Extract Twitter Followers in Bulk

Get Google SERP Results in Bulk

📢 Hungry for more deals? Visit CashKaro stores for best cashback deals & online products to save up to ₹15,000 per month. Download the app - Android & iOS to get free ₹25 bonus Cashback!
Previous articleDunkin Donuts Tasty Deal: Get a Donut for just Rs 10
Next articleHow To Extract Twitter Followers in Bulk via Power Query? The Fastest Solution Ever

LEAVE A REPLY

Please enter your comment!
Please enter your name here

2 − two =