Power BI and Google Maps API (Address Lookup)

In this post I explain how to use Google Maps APIs to retrieve useful information out of Google Maps. The use case scenario could be getting address, postal code, etc. from existing latitude and longitude values. The data could be generated by any sort of GPS tracking device like your Garmin cycling GPS computer, your Fitbit watch etc. I know you can load your GPS tracking data into athletic social networks to analyse your activities. But, if you want to do some more specific data analytics like in which area of the city you created more power during your cycling activities then those websites might not give you what you want for free.

For instance, you can export your device data to CSV then import and append all CSV files into a Power BI model and create amazing analytical reports. How to import your CSV files into a Power BI model is out of scope of this article so I leave it to you for any further investigations.

GPS tracking devices are creating lots of data including geographic coordinates which can be easily used in Power BI. You can simply put latitude and longitude on a Map visualisation and you’re good to go.

Power BI Map using Coordinates

You can also concatenate the latitude and longitude data and use it as Location in your Map visualisation.

Power BI Map using Location

This can be done from Query Editor in M language.

Creating Location from Latitude and Longitude in Power BI

But, in some cases you need some more geo-information like Country, City, Post Code and Street Address in a table as well. Or you might want to use postal code in a slicer. In this article I show you how to get all of these information out of Google Maps by passing existing coordinates to Google Maps geocoding API.

Continue reading “Power BI and Google Maps API (Address Lookup)”

Data Visualisation with Power BI Desktop

As most of you guys know Power BI Desktop is released. I should say, it’s awesome. There are heaps of changes in compare with its preview edition Power BI Designer. I’ve written a series of posts regarding creating a report and dashboard using Power BI Designer before. You can find them here. Now I want to explain the same thing in Power BI Desktop. I’ll cover lots of new features in this post and I hope you enjoy it.

Get Data

  • Open Power BI Desktop
  • Click on Get Data. You can also get data from recent data sources or even open a predefined report stored in pbix format

Power BI Desktop 01

  • We use Adventure Works DW 2012 database as sample, you can open your real world data source
  • Click on “SQL Server Database” then “Connect”
  • In this sample we are connecting to a “SQL Server Database”
  • Click “Connect”

Power BI Desktop 02

Continue reading “Data Visualisation with Power BI Desktop”

Power Query and SQL Server Stored Procedures

Today I want to explain how you can pass parameters to a SQL Server stored procedure. I myself was looking for a way to pass parameters to a SQL Server stored proc from Power Query. I spent a lot of time to search for a good article over the internet that explains how I could pass parameters to a stored proc from Power Query and show the results in Excel. But, I couldn’t find that much information around this as I expected. So, I decided to do some work around and you can read the results in this post. To simplify the solution, I’m going to use uspGetBillOfMaterials stored procedure in AdventureWorks 2012 database. The stored procedure accepts an integer number as ProductID and a date as CheckDate. So we need to pass two parameters to uspGetBillOfMaterials  to get the results.

If we execute the stored proc in SSMS using

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

, we’ll get the following result:

image

Now, lets go to do some works on Power Query. So open Microsoft Excel and go to Power Query tab and select SQL Server database.

image

Now type Server, Database and SQL Statement, then click OK.

Continue reading “Power Query and SQL Server Stored Procedures”