How to Interface with Risk Modeler APIs within Microsoft’s Power Query to Work with Your Risk Data within Tools Like Excel and Power BI

Power Query is an ETL tool native to a range of Microsoft products, including Excel and Power BI. It enables rapid data import from multiple sources and cleaning of data right in Excel.

The Power Query Editor’s user-friendly user interface can be used to interactively import and perform transformations on data using the underlying “M” query execution engine, which runs queries expressed in the Power Query formula language (“M”).

When you create a new import or transformation by interacting with the components of the Power Query interface, M code is automatically generated. Alternatively, users can write M code directly.

Power Query offers many data source options for imports, such as files or databases, but the option to import data from the web is of particular interest because this mechanism can be used to pull data directly from RMS Intelligent Risk Platform REST APIs.

Prerequisite 

For this tutorial to work, you’ll need:

• Microsoft Excel 2010 or more recent on a Windows machine

Import Data from Web via Power Query

Let’s take the use case where you want to import location details from Risk Modeler into Excel. To illustrate the process, let’s start by stepping through importing data through the user interface.

• Within Excel, go to the Data menu ribbon and click on From Web in the Get & Transform Data menu.
• Create an Advanced request with the URL parts as the GET request you’d like to make and the Authorization header as your API key.

• Click OK to make the request. This returns a preview of the imported data.

• Navigate to the property record by double clicking on Record.

• Convert the query results into a table by clicking Into Table. 

The steps we just walked through are listed as Applied Steps on the right. You can perform additional transformation steps on the data here in the Power Query Editor.

Bring the Power Query Results Into Excel

• Once you are comfortable with your table, click Close & Load beneath the Home menu ribbon.

• Power Query will bring your results into Excel and the data may be viewed and manipulated as normal.

Edit Query in the Advanced Editor

• To see the M code being generated behind the user interface, reopen the Power Query Editor and select Advanced Editor from the Query menu.

The same steps listed in the Applied Steps are reflected in the code. You are able to edit the code in the Advanced Editor.

Import Data from an Advanced Query

The user interface was sufficient for the single API GET request, but the user also has the option to write their own query directly to handle more complex use cases. Let’s write an advanced query to obtain a security token and use that token to make the same Risk Modeler API request that was made above.

• Within Excel, go to the Data menu ribbon and click on Blank Query and then Advanced Editor.
• Insert the following example:
  

  

  Copy to

  

        
          let
  
      url = "https://api-euw1.rms-ppe.com/sml/auth/v1/login/implicit",
  
      body = "{
  
      ""tenantName"": ""<RI tenant name>"",
  
      ""username"": ""<RI User Name>"",
  
      ""password"": ""<RI Password>""
  
      }",
  
  
      // Obtain a bearer token
  
      GetJson = Web.Contents(url,
  
          [
  
              Headers = [#"Content-Type"="application/json"],
  
              Content = Text.ToBinary(body)
  
          ]
  
      ),
  
      FormatAsJson = Json.Document(GetJson),
  
  
      // Gets token from the Json response
  
      Access_Token = FormatAsJson[accessToken],
  
      AccessTokenHeader = "Bearer " & Access_Token,
  
  
      Source = Json.Document(Web.Contents("https://api-euw1.rms-ppe.com/riskmodeler/v1/locations/2/?datasource=s15", [Headers=[#"Authorization"=AccessTokenHeader]])), property = Source[property]
  
  
  in
  
      property
        
      

  let
  url = "https://api-euw1.rms-ppe.com/sml/auth/v1/login/implicit",
  body = "{
  ""tenantName"": ""<RI tenant name>"",
  ""username"": ""<RI User Name>"",
  ""password"": ""<RI Password>""
  }",
  
  // Obtain a bearer token
  GetJson = Web.Contents(url,
  [
  Headers = [#"Content-Type"="application/json"],
  Content = Text.ToBinary(body)
  ]
  ),
  FormatAsJson = Json.Document(GetJson),
  
  // Gets token from the Json response
  Access_Token = FormatAsJson[accessToken],
  AccessTokenHeader = "Bearer " & Access_Token,
  
  Source = Json.Document(Web.Contents("https://api-euw1.rms-ppe.com/riskmodeler/v1/locations/2/?datasource=s15", [Headers=[#"Authorization"=AccessTokenHeader]])), property = Source[property]
  
  in
  property

  

  

  Copy to

  

• Replace tenantName with your subdomain name used to access  RMS Intelligent Risk Platform. As an example, if you use the URL https://mytenant.rms.com, the tenantName is “mytenant.”
• Replace the username and password with the credentials for a RMS Intelligent Risk Platform user account.
• Click Done.
• You may be prompted on how to authenticate. Click Edit Permissions and use Anonymous, leaving the URL as populated.
• You may be prompted about data privacy.

• You may select to Ignore Pricy level checks and Save. This will allow for the connection to be made with the retrieved access token.

Bring the Power Query Results Into Excel

• Once you are comfortable with your table, click on Close & Load beneath the Home menu ribbon.
• Power Query will bring your results into Excel and the data may be viewed and manipulated as normal.

• To refresh the data, click on Refresh within the Queries & Connections section of the Data menu ribbon
• Save your Excel file.
• When opening the file or with a Refresh, a new access token will be retrieved to use for the query.

Writing queries that automate data import and transformation can significantly reduce time spent on extracting data from Risk Modeler and manually importing and manipulating it in Excel, while also eliminating common copy and paste errors. The two samples above both quickly imported the same location data, and they show how easy it is to get started using Risk Modeler APIs right in Excel.