Power Query MySQL database connection

Excel Power Query can make a connection to MySQL database but requires that you have a MySQL Connector/Net 6.6.5 for Microsoft Windows. Instructions for that on Microsoft site.

Once you have the connector you can get MySQL db table data into your Excel file using Power Query in two ways.  The method you select will be dependent on how you want to work with the MySQL data you retrieve.

The choice is made after you have selected Power Query  – From Database – From MySQL Database (screenshot below).

power query mysql database

After you select From MySQL Database, you will see the MySQL Database connection popup.

Time for a choice to be made: For both methods you have to specify the Server and Database, but you can optionally enter the SQL Statement (method #1) or not (method #2).

power query mysql connect

Method # 1. Native SQL query – when connecting you have option to enter SQL, and if you enter SQL query there you will get:

let
    Source = MySQL.Database(“127.0.0.1″, “database_name”, [Query=”select * from database_table_name”])
in
    Source

Method # 2. Power Query Navigation – instead of entering SQL, just leave that field blank, then continue on, and Power Query will present you with list of MySQL server database names (Schemas) and tables names.  Simply select the “Table” link in the database and table that you want and that will add Navigation step and retrieve that table’s data.

let
    Source = MySQL.Database(“127.0.0.1″, “database_name”),
   database_table_name = Source{[Schema=”database_name”,Item=”database_table_name”]}[Data]
in
    database_table_name

Either way you get the same table results in the Power Query.

Of course, if you want to join in other tables from your MySQL database(s), then method #1 will be more direct. Method #1 also assumes that you can use SQL to get what you want from the MySQL database tables.

However, you could also use method #2 to retrieve all desired tables (even from different databases on that server), and then use Merge or Append to get the desired results.

Method #2 allows you to retrieve and work with your MySQL database table data without using SQL and rely on Excel Power Query instead.  That opens the door for relatively non-technical data workers to use the data which is a pretty cool thing!

You can use these methods with MySQL databases on your local computer or on a remote computer.  You just have to make sure to enter the correct server url, database name, user and password.

On occasion i have had challenges before I could get a remote MySQL database connection to work.  I had to clear the Power Query cache, update Power Query, turn off the Privacy option to make the connection work.

 

No Comments

Toronto 311 call centre data

Toronto 311 call centre data  is published real time via API.  Call data from 2010 to 2013 was downloaded and reformatted in the attached Excel file with data and pivot tables and charts.  Screenshots of the charts are attached below.

311-chart-by day from 2010 to 2013 shows consistency seasonally over the entire period of time and for call dispositions.

The call centre seems to be doing a pretty good job of answering calls by staffing for expected volume.

311-chart-by day

311-chart-by day of week

311-chart-by day of week

 

 

 

 

 

311-chart-wkday vs wkend

311-chart-wkday vs wkend

311-chart-yearmonth

311-chart-yearmonth

311-call relative volume

311-call relative volume

No Comments

Human arterial blood flow Sankey diagram

Data sourced from Wikipedia human arterial tables and the D3 visualization library were used to create a Sankey diagram that shows arterial blood flow from the heart to the body.

The Sankey diagram is very big at 1500 pixels wide and 8000 pixels tall.

Chart features:

  • Hover over any link to highlight the link and see the artery-source names, and relative flow value.
  • Click on any node eg artery name to highlight the upstream and downstream links. Click again to remove the highlighting (or refresh page). Can click multiple nodes.
  • Drag and drop nodes eg artery names along vertical axis to position them to read them etc.

Screenshots of it below.

First screenshot shows Left Ventricle flow to the body

 

sankey_screenshot_1

Second screenshot shows Right Ventricle flow to the lungs

sankey_screenshot_2

Next steps would be to build the Sankey out to show flow back to the heart through veins.  If the venous blood system was included, you could imagine it as a mirror image starting where the arterial system leaves off, and then flow back to the heart.

Also, it would be nice to find values that could quantify flow eg the heart would be 1 and rest of the system in fractions.

 

 

No Comments

Toronto Dine Safe ratings mapped with Tableau

The City of Toronto Dine Safe program publishes ratings data bi-weekly.  This data includes venue street address but not latitude and longitude. So the data had to be geocoded before it could be used to create the Tableau public dashboard below.  I used MapQuest’s Nominatim geocoding web service and python script to get lat and lon for all addresses in the data file.  Nominatim has a daily address geocoding limit so the script ran in daily batches over a few days to geocode all addresses.

No Comments

Canadian inter-provincial migration Jan 1971- Oct 2014

Statistics Canada inter-provincial migration data for movement between provinces from Jan 1971 to Oct 2014 was used to create a Sankey diagram illustrating aggregated flow of people from province to province below.

The Sankey Chart is created using D3.

 

No Comments