2016-07-05

Power BI & Telstra Wifi Hotspot API

A while ago I wrote the post to show how to call Telstra Wi-Fi Hotspot API through C#. Today let’s see how easy we can achieve the same result (even better) by using Microsoft Power BI.


Power BI, in short, is an powerful BI tool for data extraction, transformation, and visualisation.  I would highly recommend it if you haven’t tried it yet.  At this stage you can sign it up for free, by applying a trial office 365 license.


Now back to the topic, today my plan is to call the API by using Power Query embedded in Power BI, and then display the result on the workspace. To do so let’s open Power BI Desktop, and press Edit Queries button on the ribbon.




On the Query Editor window (Power Query), open dropdown menu by press New Source button, then select Blank Query option.




Now click Advanced Editor button on the ribbon to open the editor window




Now Advanced Editor window pops up, and we can start to type in Power Query statements. To demonstrate how powerful the power query is, I decided to split the procedure into two: create a function to get oAuth token, and then call the Wi-Fi Hotspot API.


To create the function in Power Query, firstly let’s write below query (replace consumer key and consumer secret)




 let  
   authUrl = "https://api.telstra.com/v1/oauth/token",  
   authKey = "client_id={consumerkey}&client_secret={consumer secret}&grant_type=client_credentials&scope=WIFI",  
   getJson = Web.Contents  
       (authUrl,  
         [  
           Headers = [#"Content-Type" = "application/x-www-form-urlencoded"],  
           Content = Text.ToBinary(authKey)  
         ]  
       ),  
   jsonDoc = Json.Document(getJson)  
 in  
   jsonDoc  


Click Done button and the oAuth result should be shown on Power Query window directly, as shown below




So far everything is good. What we need to do now is to introduce parameters into the query, and then parameterise the query. To do so firstly create new parameters




For demonstration purpose, I treat consumer key and consumer secret as parameters, as shown below






Now change our query a bit, I have highlighted changes in the screenshot attached below




Once you done everything and click Done button, the function window appears




Now you can test the function by entering consumer key and consumer secret then pressing the Invoke button.




The function should work as expected. Time to write the 2nd script. Following the same step to open a blank query window and input statements like below.




 let  
   accessToken = fnGetToken("{consumerkey}", "{consumersecret}")[access_token],  
   latitude = "-37.8103713989",  
   longitude = "144.99530273",  
   radius = "1000",  
   wifiUrl = Text.Format("https://api.telstra.com/v1/wifi/hotspots?lat=#{0}&long=#{1}&radius=#{2}", {latitude, longitude, radius}),  
   accessTokenHeader = "Bearer " & accessToken,  
   getWifiJson = Web.Contents(wifiUrl, [Headers = [#"Authorization" = accessTokenHeader]]),  
   wifiJson = Json.Document(getWifiJson),  
   table = Table.FromList(wifiJson, Splitter.SplitByNothing(), null, null, ExtraValues.Error),  
   wifiTable = Table.ExpandRecordColumn(table, "Column1", {"lat", "long", "address", "city", "state"}, {"lat", "long", "address", "city", "state"})  
 in  
      wifiTable   


Now things could be a little bit tricky. Depends on your Power BI desktop setting, you might get the result without any issue. But in most cases, you could see two different types of warnings: connection warning and privacy warning. In short it is because we are passing oAuth token from a different Url (https://api.telstra.com/v1/oauth/token vs https://api.telstra.com/v1/wifi/hotspots). Let's see how we can resolve these conflicts:


The first warning you might see is the connection warning.




Click the button “Edit Credential” will lead us to a popup window. Because we retrieve our token by passing consumer key and consumer secret into the function, we can simply select Anonymous method and then select the Url https://api.telstra.com/v1/oauth/token as the target.




The next window is about data privacy.




On the Privacy Level screen, select the Url https://api.telstra.com/v1/oauth/token, and then select “Public” as the scope.




After all these steps, the data table should be generated automatically




Now we can go back to the Power BI by clicking the button “Close & Apply” at the top left corner. Drag a map visual onto the workspace, drop Lat and Long columns onto the corresponding map fields, and that is it