WordPress Reporting using Power Query - Excelerator BI

WordPress Reporting using Power Query

I recently created a Book Shop on my WordPress website using WooCommerce.  The next logical thing I wanted to do was to get an extract of all my orders/invoices that have been sold through the shop into Excel.  I couldn’t find a way to do this in the WordPress UI or via a free plug in, and then it occurred to me that maybe I could extract the data from the underlying WordPress Database using Power Query – and indeed you can.  This is how I did it.

Enable Remote Access to your Database

Log into cPanel for your website and go to “Remote mySQL”


By default there should be one IP address set up for access – this is your web server.  You need to add your home IP address to the list (or the IP address from where ever you are located and want to access the DB).  You can find out your IP address at http://www.whatsmyip.org/.  If you have a static IP address, you may want to use a service like Dyndns or similar  http://lifehacker.com/the-best-free-alternatives-to-dyndns-1561556205 to maintain a static URL.  This is not essential; the other approach you could use is to just update your IP address each time you need to.


Power Query Online Training

Connect to the Database with Power Query

Open Excel, go into Power Query and commence the import process


Enter your Server Name and Database Name.  For the server, you can either use the web site IP address you found when you first opened cPanel (not your home IP address) or you can use your main hosting website address.  If in doubt, contact your ISP and ask them.  The database name is the name of the WordPress DB you set up when installing your account.  You can find this by going into cPanel and selecting “mySQL Databases”


The first time I did this, I got an error saying I didn’t have the right drivers.

mySQL connector error

I just followed the link in the error, downloaded the drivers and installed them.


After installing the drivers, I tried to log in – and it worked fine.

Find the tables you need and import them

You can use this approach for extracting any data from your WordPress DB.  In my case, I was looking for the WooCommerce transaction tables so I could get a list of my invoices. I downloaded the following tables


Build a Data Model in PowerPivot

I joined the tables using the Order ID and order Item ID as shown below


Power BI Online Training

I then built some simple DAX measures for Quantity, Price etc as follows


Total Products := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_qty”)

Product Val Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_tax”)

Product Val ex Tax := CALCULATE(sum(Detail[meta_value]),Detail[meta_key]=”_line_subtotal”)

Product Val incl Tax :=[Product Val Ex Tax]+[Product Val Tax]

Power BI DAX Book

In WooCommerce, I set up the invoice number to be the Order ID with a prefix ‘s’.  I just repeated that same logic in is a calculated column in DAX that follows the same rules.

And I ended up with a Pivot Table Report like this.


Hope that helps someone experiment with accessing WordPress Data using Power Query.

If you want a comprehensive lesson on how to use Power Query, check out my training course here https://exceleratorbi.com.au/power-query-online-training/

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top