How to integrate Motimate Public API to PowerBI?

Wish you could always have live and up to date dashboards and analytics for your Motimate data? Look no further - with our Public API, you can get all your API data directly into tools like Microsoft's PowerBI. Here we show you how to set this up! ✨

Before you go further, ensure your MotiSpace has the Public API enabled. If not, find out more here.

Generate an Access Token in Postman

• Open Postman and make a POST API call to the authentication endpoint to obtain a token.
  
  
• Copy the generated token for temporary use in PowerBI.

Now use the Access Token to retrieve data in PowerBI

• Open Power BI and click Get Data --> Select Web --> Click Connect.
  
  
  
  
• In the Web connection window, select Advanced.
  
  
• Paste the API URL of the data source
  ie. https://motimateapp.com/public_api/my_organization
  
  
• In the Headers section, add:
  • Key: Authorization
  • Value: "Bearer <<Token generated from Postman>>"
    
    
    
    
• Click OK to proceed.

The Power Query Editor will open, displaying the retrieved data.

Create a Query to Generate Tokens Dynamically

Since Motimate Public API tokens expire after a 24 hours, PowerBI must dynamically generate a new token before each API call to ensure the token is always valid. This can be set up by following these steps:

• In Power Query Editor, click New Source --> Blank Query.
  
  
  
  
• Once the blank query opens, go to the Advanced Editor.
  
  
  
  
• Enter the following Power Query M query, and replace the bold text with your own credentials:
  
  

  let
  url = "https://motimateapp.com/public_api/oauth/token",
  headers = [#"Content-Type" = "application/json"],
  postData = Json.FromValue([
  
  client_id="<<your client ID>>", 
  client_secret="<<your client secret>>", 
  grant_type="client_credentials"
  
  ]),
  response = Web.Contents(
  url,
  [
  Headers = headers,
  Content = postData
  ]
  ),
  Data = Json.Document(response),
  access_token = Data[access_token]
  in
  access_token
  
  

• Click Done.
  
  

• Verify the applied steps to confirm a token is being generated:
  
  

Convert the Query to a Function

• Now rename the query to "GetToken".
  
  
  
  
• Right click on GetToken and open the Advanced Editor
  
  
  
  
• Modify the query by adding "() =>" at the beginning (before "let"). This will convert the query into a function.
  
  
  
  
• Click Done.

Replace the Hardcoded Access Token with the Function

• Open the main dataset query in the Advanced Editor.
  
  
  
  
• Find the hardcoded token in the "Headers" section, and replace the token itself with the GetToken() function. It should now look like this:
  
  Authorization = "Bearer "&GetToken()
  (ensure that you leave a space between the word Bearer and the trailing quotation mark, as shown above)
  
  
  
  
• Click Done.
• Finally, click Close & Apply.
  
  

Congratulations! You have set up a report in PowerBI with a dynamic token logic 🎉

Now, PowerBI will dynamically generate a token before every API call, ensuring seamless data updates without manual intervention. This approach simplifies data retrieval and improves automation.