FeedbackNewsletterSupportBecome a partner

Power BI Custom Connectors

How to set up a Power BI Custom Connector on top of Samsara's API endpoint

Suggest Edits

Introduction

Samsara customers have the ability to collect vast amounts of data and gain valuable insights into their connected operations environments out-of-the-box via the Samsara dashboard and API. Many times, customers also regularly utilize data analysis, visualization, and business intelligence platforms from third party software vendors. Microsoft's Power Business Intelligence (BI) + Power Query M Language are popular choices among their reporting toolkits.

Through the combined use of Samsara's RESTful API and Microsoft's Power Query Editor, it is possible to quickly set up Samsara as a data source to allow customers to create their own reports or dashboards in Microsoft reporting/analytics tools. This guide will provide a couple of generic ways to accomplish this. If you find better ways to accomplish these, please let us know. We would love your feedback!

📘

Disclaimer

The examples provided in this guide are one of many ways to utilize the Samsara API as a data source via Microsoft Power Query Editor. For example - as you may observe, the parameters utilized in some of these queries can be hardcoded as variables within the queries if no user input is desired when running.

If customers find better ways to accomplish these, please let us know!

👍

Prerequisites

  • Before diving into the specifics, if you aren't familiar with M code and building customer Power BI connectors using Power Query, please review the video below:
    • "Deep Dive into the M Language", YouTube, uploaded by Microsoft Power BI, 13 June 2017

Below are the timestamps for specific topics from the above demo video.

Utilizing Microsoft Power Query Editor

👍

Prerequisite

Power Query Editor can be found built-in to many versions of Microsoft data tools such as Excel and Power BI Desktop.

  • Typically, the way to access the Samsara API via this tool is to find the "Get Data" menu option, select "From Other Sources", and create a "Blank Query"
  • While it is possible to utilize other Data Source types like "From Web", we have found that it is the easiest to start with a Blank Query

Power Query Examples

Example 1: Trips API Integration

Samsara telematics devices allow customers to track where their fleets have been going - one relevant metric is a Trip (i.e. the distance traveled, start, and end destination of a given vehicle). This data is available via the /v1/fleet/trips API endpoint.

👍

Prerequisites for Trips Example (Basic; v1 endpoint) query

  • A Samsara API token
    • A beginning timestamp in Unix time (startMs referenced in the query below; e.g. 1654066800000)
    • An ending timestamp in Unix time (endMs referenced in the query below; e.g. 1656658799000)
    • One Samsara vehicle ID

The vehicle IDs referenced here are Samsara-generated internal IDs. These can be found by either calling on the GET /fleet/vehicles API endpoint or by clicking on a vehicle in your Samsara fleet via the Samsara dashboard and examining the number (usually starting with a 2) that comes after /devices/ in the URL.

let parameters = (startMs as text,endMs as text, vehicleId as text) =>
    let
        accessToken = "YOUR ACCESS TOKEN",
        baseUrl = "https://api.samsara.com/",
        endpointUrl = "/v1/fleet/trips",
        initialRequest = Json.Document(Web.Contents(baseUrl,[RelativePath = endpointUrl & "?startMs=" & startMs & "&endMs=" & endMs & "&vehicleId=" & vehicleId, Headers = [Authorization = "Bearer " & accessToken]]))
    in
        initialRequest
in
parameters

Power BI Desktop Integration Example.

  • Create a new Power BI project and select “Blank Query” under “Get Data” and click “Connect”.
894
  • Select "Query" and then navigate "Advanced Editor" and paste the above code snippet in the query box. (Note: Please add the Samsara API Key in the example)
  • Click "Done".
  • Once the above steps are completed, you will be prompted to enter "startMs", "endMs" and "vehicleId" parameters. Enter these fields and click "invoke". (Refer to the above prerequisites for this example regarding these parameters)
1234 1244
  • If the API key is already entered in the Query then click "connect" if not enter the API credentials.
1240
  • If the Query is executed successfully, the output will be in a "list" format.
1244
  • To Convert the output into a table, click on "To Table" and then select "OK"
1206
  • The Output will be in a Tabular format. Select the columns which you want to display in your table and click "OK".
1246
  • To perform further data transformations, you can also edit the Table query.
1240

Expanding the above Trips query into a Table via Function
This query should return a List of Trips. In Power Query Editor, you'll be able to convert this list into a Table and from there, expand the lists into additional Columns. Once you've expanded out all the Columns, you should end up with a Table containing all objects returned from your query. In doing this, you will have created a Function in Power Query Editor. The Function should look similar to the example below:

let
    Source = Query1("VALUE FROM startMs PARAMETER", "VALUE FROM endMs PARAMETER", "VALUE FROM vehicleId PARAMETER"),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
    #"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"startMs", "endMs", "startLocation", "endLocation", "startAddress", "endAddress", "startCoordinates", "endCoordinates", "distanceMeters", "fuelConsumedMl", "tollMeters", "driverId", "codriverIds", "startOdometer", "endOdometer", "assetIds"}, {"Value.startMs", "Value.endMs", "Value.startLocation", "Value.endLocation", "Value.startAddress", "Value.endAddress", "Value.startCoordinates", "Value.endCoordinates", "Value.distanceMeters", "Value.fuelConsumedMl", "Value.tollMeters", "Value.driverId", "Value.codriverIds", "Value.startOdometer", "Value.endOdometer", "Value.assetIds"}),
    #"Expanded Value.startAddress" = Table.ExpandRecordColumn(#"Expanded Value1", "Value.startAddress", {"id", "name", "address"}, {"Value.startAddress.id", "Value.startAddress.name", "Value.startAddress.address"}),
    #"Expanded Value.endAddress" = Table.ExpandRecordColumn(#"Expanded Value.startAddress", "Value.endAddress", {"id", "name", "address"}, {"Value.endAddress.id", "Value.endAddress.name", "Value.endAddress.address"}),
    #"Expanded Value.startCoordinates" = Table.ExpandRecordColumn(#"Expanded Value.endAddress", "Value.startCoordinates", {"latitude", "longitude"}, {"Value.startCoordinates.latitude", "Value.startCoordinates.longitude"}),
    #"Expanded Value.endCoordinates" = Table.ExpandRecordColumn(#"Expanded Value.startCoordinates", "Value.endCoordinates", {"latitude", "longitude"}, {"Value.endCoordinates.latitude", "Value.endCoordinates.longitude"}),
    #"Expanded Value.codriverIds" = Table.ExpandListColumn(#"Expanded Value.endCoordinates", "Value.codriverIds"),
    #"Expanded Value.assetIds" = Table.ExpandListColumn(#"Expanded Value.codriverIds", "Value.assetIds")
in
    #"Expanded Value.assetIds"

From here, you'll be able to "Close & Load" your Data Source, which will populate your Excel sheet or Power BI Report with the data you've pulled via Power Query Editor.

Example 2: Safety Events API Integration (with Pagination)

With Samsara's List All Safety Events API endpoint, it's possible to retrieve your entire fleet's (or a sub-section of it defined individually by a list of vehicles or Tags) Safety Events within a defined time frame.

👍

Prerequisites for Safety Events Example (v2 endpoint with Pagination) query

  • A Samsara API token
    • A startTime in RFC3339 format (startTime referenced in the query below; e.g. 2022-06-01T00:00:00-08:00)
    • An endTime in RFC3339 format (endTime referenced in the query below; e.g. 2022-06-30T00:00:00-08:00)

Please make note that unlike the Trips v1 API endpoint, the List All Safety Events v2 API endpoint returns responses in separate pages (i.e. with pagination). Your query will need to account for this by checking for the "hasNextPage" value; if it is "true", this indicates there are subsequent pages with data that need to be requested by assigning the returned "endCursor" as the value for the "after" parameter in the following call until "hasNextPage" returns "false".

let parameters = (startTime as text,endTime as text) =>
    let
        accessToken = "YOUR ACCESS TOKEN",
        baseUrl = "https://api.samsara.com/",
        endpointUrl = "fleet/safety-events",
        initialRequest = Json.Document(Web.Contents(baseUrl,[RelativePath= endpointUrl&"?startTime="&startTime&"&endTime="&endTime, Headers=[Authorization = "Bearer "&accessToken]])),
        nextUrl = if initialRequest[pagination][hasNextPage] = false then null else initialRequest[pagination][endCursor],
        initialValue= initialRequest[data],
        dataCollection=(data as list, url)=>
            let
                newRequest=Json.Document(Web.Contents(baseUrl,[RelativePath= endpointUrl&"?startTime="&startTime&"&endTime="&endTime&"&after="&url, Headers=[Authorization = "Bearer "&accessToken]])),
                newNextUrl = if newRequest[pagination][hasNextPage] = false then null else newRequest[pagination][endCursor],
                newData= newRequest[#"data"],
                dataCombined=List.Combine({data,newData}),
                check = if newRequest[pagination][hasNextPage] = true then @dataCollection(dataCombined, newNextUrl) else dataCombined
            in
                check,
                outputList= if initialRequest[pagination][hasNextPage]= true then @dataCollection(initialValue,nextUrl) else initialValue
    in
        outputList
in
    parameters

Refer to this example for executing the above query in Power BI Desktop.

Expanding the above Safety Events query into a Table via Function
Following the same Steps in Power Query Editor as in the Trips example, you can expand out the initially-returned List of Safety Events into additional Columns.

let
    Source = Query1("VALUE FROM startTime PARAMETER", "VALUE FROM endTime PARAMETER"),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "driver", "vehicle", "time", "maxAccelerationGForce", "downloadForwardVideoUrl", "location", "coachingState", "behaviorLabels"}, {"Column1.id", "Column1.driver", "Column1.vehicle", "Column1.time", "Column1.maxAccelerationGForce", "Column1.downloadForwardVideoUrl", "Column1.location", "Column1.coachingState", "Column1.behaviorLabels"}),
    #"Expanded Column1.driver" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.driver", {"id", "name"}, {"Column1.driver.id", "Column1.driver.name"}),
    #"Expanded Column1.vehicle" = Table.ExpandRecordColumn(#"Expanded Column1.driver", "Column1.vehicle", {"id", "name"}, {"Column1.vehicle.id", "Column1.vehicle.name"}),
    #"Expanded Column1.location" = Table.ExpandRecordColumn(#"Expanded Column1.vehicle", "Column1.location", {"latitude", "longitude"}, {"Column1.location.latitude", "Column1.location.longitude"}),
    #"Expanded Column1.behaviorLabels" = Table.ExpandListColumn(#"Expanded Column1.location", "Column1.behaviorLabels"),
    #"Expanded Column1.behaviorLabels1" = Table.ExpandRecordColumn(#"Expanded Column1.behaviorLabels", "Column1.behaviorLabels", {"label", "source", "name"}, {"Column1.behaviorLabels.label", "Column1.behaviorLabels.source", "Column1.behaviorLabels.name"})
in
    #"Expanded Column1.behaviorLabels1"

Example 3: Historical Vehicle Stats API Integration (with Pagination)

Similarly, with Samsara's Historical stats API endpoint, it's possible to retrieve your entire fleet's(or a sub-section of it defined individually by a list of vehicles or Tags) Vehicle Stats within a defined time frame.

👍

Prerequisites for Historical Vehicle Stats Example (v2 endpoint with Pagination) query

  • A Samsara API token
    • A startTime in RFC3339 format (startTime referenced in the query below; e.g. 2022-06-01T00:00:00-08:00)
    • An endTime in RFC3339 format (endTime referenced in the query below; e.g. 2022-06-07T00:00:00-08:00)
    • Vehicle stats types (up to three comma-separated stats types per call) listed under the "types" array in the API reference for the /vehicle/stats/history endpoint; e.g. engineStates or gps
let parameters = (startTime as text,endTime as text,statsType1 as text) =>
    let
        accessToken = "YOUR ACCESS TOKEN",
        baseUrl = "https://api.samsara.com/",
        endpointUrl = "fleet/vehicles/stats/history",
        initialRequest = Json.Document(Web.Contents(baseUrl,[RelativePath= endpointUrl&"?startTime="&startTime&"&endTime="&endTime&"&types="&statsType1, Headers=[Authorization = "Bearer "&accessToken]])),
        nextUrl = if initialRequest[pagination][hasNextPage] = false then null else initialRequest[pagination][endCursor],
        initialValue= initialRequest[data],
        dataCollection=(data as list, url)=>
            let
                newRequest=Json.Document(Web.Contents(baseUrl,[RelativePath= endpointUrl&"?startTime="&startTime&"&endTime="&endTime&"&types="&statsType1&"&after="&url, Headers=[Authorization = "Bearer "&accessToken]])),
                newNextUrl = if newRequest[pagination][hasNextPage] = false then null else newRequest[pagination][endCursor],
                newData= newRequest[#"data"],
                dataCombined=List.Combine({data,newData}),
                check = if newRequest[pagination][hasNextPage] = true then @dataCollection(dataCombined, newNextUrl) else dataCombined
            in
                check,
                outputList= if initialRequest[pagination][hasNextPage]= true then @dataCollection(initialValue,nextUrl) else initialValue
    in
        outputList
in
    parameters

Refer to this example for executing the above query in Power BI Desktop.

Query Package and Deploy

Expanding the initial query into a Table via Function
Following the same Steps in Power Query Editor as in the Safety Events example, you can expand out the initially-returned List of Safety Events into additional Columns. The Table conversion + Column expansion Function for this particular query will vary depending on which statsTypes are requested.
The Microsoft M video in this guide goes over these details from 34:46 onwards.

Custom Connector Limitations

Samsara collects a large volume of data. Depending on the API endpoint you use, you may be retrieving a large volume of data that could hit the default storage limitation that different Power BI tiers have. For example, Samsara's GET /fleet/vehicles/stats API return dense GPS data per vehicle.

When building out your connector, be sure to validate how much historical data you are looking to query from Power BI. Based on this analysis, you may find it more economic to build out a data warehouse by calling Samsara APIs and then connecting that data warehouse to Power BI instead a direction connection to Samsara via a custom Power BI connector.

Updated 6 months ago