logo image

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.

rms

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

rms

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

rms

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

rms

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.

rms

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.

rms

rms

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

rms

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:
    Close
    Expand

    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
    Close
    Expand

    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.

rms

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

rms

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.

rms

  • 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.

Alexandria Julius
Alexandria Julius
Technical Consultant

Alexandria Julius is a technical consultant on RMS’s Consulting team. As a technical consultant, she works on custom solution implementations to integrate clients with RMS products. She is responsible for workflow design, custom software development, testing, and reporting. She holds a BS in Engineering Science from Smith College and a MS in Geodetic Engineering and Remote Sensing from The Ohio State University. Alexandria is based in the RMS Hoboken, New Jersey office.

Tech Banner

Questions or Feedback on Current Blog Posts? Suggestions for Upcoming Blog Posts?

close button
Overlay Image
Video Title

Thank You

You’ll be contacted by an RMS specialist shortly.