Use your API in PowerBI

In this example I will show how to get your Board data in PowerBI using the Board API.


Board is using an authorization method called OAuth2.0, which is a common used standard for APIs, however there is no default connector in PowerBI, so we will need to build our own query.


Open PowerBI desktop.

  1. select the tab Home
  2. Press the button Get Data
  3. Select the option Blank Query.



You will get a new screen.

  1. Give your query a name e.g. Board_API
  2. Press the button Advanced Editor.



You will get a new screen, here you will have to add the following query:


In the token_URL, you need to enter the URL, for getting the token, see also the chapter testing your API.

In the api_base_URL, you need to enter your BOARD API

In the line with body.... you need to enter your Board API user and your Client secret.


let

    token_url = "https://<BOARD SERVER>/identity/connect/token",    

    api_base_url = "https://<BOARD SERVER>/public/Board_Demo/query/Board_PowerBI",

    qry_str = "?myparameter",


   body="grant_type=client_credentials&Scope=public-api&client_id=<BOARD API USER>&client_secret=<CLIENT SECRET>",

   Source  = Json.Document(Web.Contents(token_url,

   [ 

     Headers = [#"Content-Type"="application/x-www-form-urlencoded"],

     Content=Text.ToBinary(body)

   ]

   )

   ),

    token = Source[access_token],


    data= Json.Document(Web.Contents(api_base_url,

   [ 

     Headers = [

                #"Authorization"="Bearer "&token,#"Content-Type"="application/json"]

     

   ]

   )

   )

in

  data


The query will first call the URL to get the token and will store it in a variable, then it will call the Board API, pass the token and retrieve data, which is stored in a variable called data 


After you entered your query and press the done button you will get the following screen.


  1. Press on List in the row DATA



You will get the following screen.

  1. Press the button To Table
  2. You will get a new screen, you don't have to change here anything.
  3. Press the OK Button.


You will get the following screen.

  1. Press the button with the two arrows next to Column1 
  2. select the value Extract Values...


You will get the following screen.

  1. Select a customer delimiter
  2. Enter "," as the delimiter, In the Board API all fields are between quotes (") and the fields are separated with a comma, if you would use only the comma as a separator then it is possible that entities values with a comma would be separated into two columns.


You will get the following screen.

PowerBI will extract the data from your API, but it is still stored in one column.

  1. Press the button Split columns.
  2. select the option by Delimiter and in the pop-up screen use the "," as the delimiter.



You will get the following screen.

  1. Rename your column headers.
  2. Press the button Close and Apply.


Your are back in the main screen, on the right you will see your new connector.

  1. Select for example the the matrix
  2. In your rows you select the GL_Description
  3. In your columns your select the months
  4. In your values you select the values.


Now the ultimate test, make an adjustment in Board, save your data and refresh you your PowerBI screen.


After saving your data in Board press the refresh button, as you see the data in PowerBI is now updated.