What is Power Query? Power Query is an Microsoft tool for Excel that is used to do the following: Connect to a data source Extract data from source Transform the extracted data Load the transformed data into Excel This functionality is called ETL (Extract, Transform and Load). Why use Power … Continue Reading →
AWS RDS Postgres database external secure connection
Requiring an SSL Connection to a PostgreSQL DB Instance http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.SSL Not great examples on that but take a look at this other documentation for secure connection to Redshift database which has working examples http://docs.aws.amazon.com/redshift/latest/mgmt/connecting-from-psql.html Go to EC2 Security Groups, and modify the Inbound PostgreSQL rule from the VPS server eg … Continue Reading →
How to use Hadoop HDFS as a datasource for Power BI
I have a local Hadoop server running on my Windows laptop. To connect Power BI to this Hadoop file system I used Hadoop’s WebHDFS REST API which is very well documented. https://hadoop.apache.org/docs/r1.0.4/webhdfs.html The WebHDFS API specifies a url in which you specify the Hadoop root file system directory that you … Continue Reading →
How to use Python as a datasource for Power BI
Step 1 – Download latest version of Power BI to get new features (version that came out in early August 2018) Step 2 – Enable Python in the “Options – Preview Features” check the “Python support” option. Close and restart Power BI. Step 3 – Click “Get Data”, select “Other” … Continue Reading →
Comparison of my old Palm vs my new Pocket PC (from 2004)
Here is a blog post from old Word Press site I wrote back in July 7, 2004 Comparison of my old Palm vs my new Pocket PC I thought I would write this to share some of my experiences making the transition from the Palm platform to Windows CE/Pocket PC, … Continue Reading →
BC Executive Compensation Tableau Visualization
The Province of British Columbia publishes public agency executive compensation each year. The data is grouped by: Sector – Employers are categorized in one of six sectors – Colleges, Crown Corporations, Education, Health, Public Service, Universities Employer – Employers belong to one Sector. There are about 190 employers such as BC Assessment Authority, BC Housing … Continue Reading →
Business Intelligence – IT Black Box Challenge
The process of engaging IT teams can be difficult to understand for many non-technical people. IT is perceived as a ‘black box’ because its inner workings are often not-transparent. BI teams may not realize that their business side coworkers do not know how to engage them. When IT workers reach … Continue Reading →
Twitter Followers and List Membership Tracking
I was curious to track how my Twitter account’s followers and my account’s list memberships changed over time. The results of my tracking are on this webpage. Twitter Followers – Twitter followers are other Twitter accounts who follow my account. A Twitter Follower may follow my account for a few days and … Continue Reading →
Installing Hadoop on Windows 8.1
It turned out to be quite simple to install Hadoop on Windows 8.1 I have been using Hadoop Virtual Machine images from Cloudera, Hortonworks or MapR when I wanted to work with Hadoop on my laptop. However these VMs are big files and take up precious hard drive space. So … Continue Reading →
Vultr and Digital Ocean make it easy to get projects going fast
Recently I developed a Django web application for a friend. I wanted to be able to get it up and out onto the internet so she could view it and get others to do user testing. Normally I host my web applications on my Webfaction account. But I wanted to … Continue Reading →
Django form geocode submitted address to get lat, lon and postal code
One of my Django applications includes a form where user can enter and submit a property address. The user submitting the form might not know the postal code so I left it optional. However the postal code is a key piece of information for this particular application so I wanted to ensure … Continue Reading →
Leaflet.js choropleth map color by count using geoJSON datasource
I have a Django web application that needed an interactive map with shapes corresponding to Canadian postal code FSA areas that were different colors based on how many properties were in each FSA. It ended up looking something like the screenshot below. This exercise turned out to be relatively easy … Continue Reading →
Canadian Canola seed crushing more efficient at extracting canola oil
Statistics Canada regularly tweet links to various Canadian statistics. I have occasionally created quick Tableau visualizations of the data and replied with a link to my Tableau Public site. The idea is to encourage Statistics Canada to start communicating more visually and create their own visualizations for all data. Its … Continue Reading →
Chart.js tooltip format number with commas
Chart.js V2.0 is a useful javascript charting library. It looks great, has ton of features though it is new enough that there is still some work to find out how to get some relatively simple things done. In this case I wanted to format the chart’s tooltip. Tooltips are the pop-ups … Continue Reading →
Python get image color palette
I have a website called Statistics R Us (http://www.statisticsr.us) that has a screenshot of about 190 country’s national statistics agencies website home page. I created the site to host the results of comparisons of each country’s website home page features. One of the features I wanted to include was the top 5 colors … Continue Reading →
Excel Power Query tutorial using Canadian potato production statistics
This data comes from Statistics Canada. Statistics Canada download pages often provide the opportunity to modify the data structure and content before it is downloaded. For example clicking on the [Add/Remove data] link provides options select different groupings of data by provinces. I choose to group data by provinces. I … Continue Reading →
Django recreate database table
Django’s makemigrations and migrate commands are very useful to update existing database tables to reflect model changes. However if you have made many existing table column name changes, migrate will ask you a series of ‘y/N’ questions about which column names are changed. This can be tedious to cycle through … Continue Reading →
Canadian TCS FDI Officers Twitter List Member Analysis – Updated
Blog post updated to add the following: Updated Python “TCS Members Details” code to get additional information from the List members’ profiles. New Python code “TCS Members Tweets” to download all List members’ Tweets. Will provide some more analysis on this new data soon. ————————————————– The Canadian Trade Commissioner Service maintains a … Continue Reading →
Use Excel Power Query to scrape & combine Wikipedia tables
Power Query is quick and easy way to scrape HTML tables on web pages. Here is step by step on getting multiple tables from Wikipedia article and appending them into one Power Query Excel table. The Wikipedia article List of national and international statistical services has multiple tables with lists of countries’ … Continue Reading →
How to update Office 365 password in Power BI dataset refresh
I recently changed my Office 365 user password for an account that I was using for a Power BI Dataset Scheduled Refresh. The result was that my Power BI Refresh failed which looked like screenshot below. So all I had to do was update the authentication Power BI was using … Continue Reading →