Follow the steps below to connect Power BI Desktop (free) to The Layer API for data analysis and reporting.
Step 1: Download Power BI Desktop
Download and install Power BI Desktop from the official Microsoft site:
👉 Download Power BI Desktop
Step 2: Set Up the Power BI API Application in The Layer
In The Layer, go to:
Settings → Application ManagementCreate a new API Application:
Name:
Power BIDescription: (Required — must not be empty)
⚠️ If the description is left blank, you will receive an error.
Step 3: Locate Your API Tokens
Go to:
Settings → Token ManagementThe tokens for your new application should be created automatically.
If not, click "Refresh Tokens" against the Power BI app entry.
🕐 Note: API tokens are valid for 1 year.
Step 4: Assign Required User Permissions
Ensure the user account you’ll be using to access the API has the following permission:
Go to:
Settings → Users → RolesEnable: Developer API → API – Access All Areas
🌐 Step 5: Choose the API URL You Want to Use
Identify the endpoint you want to connect to.
Example URL:
https://webapi.thelayer.com/api/Opportunity/ByTenant?createdStart=01/01/2021&createdEnd=17/06/2021
You may need to include filters such as:
showOnlyOpen=truecreatedStartcreatedEnd
📘 Full API Documentation:
👉 https://webapi.thelayer.com/swagger/ui/index
📥 Step 6: Connect to The Layer API from Power BI
Open Power BI Desktop.
Click Get Data → Web.
Choose Advanced mode.
In the URL parts field, enter your final API URL.
Add the following parameter to the end of the URL:
RawJsonResponse=true
Example Final API URL
rubyCopyEdithttps://webapi.thelayer.com/api/Opportunity/ByTenant?showOnlyOpen=true&createdStart=01-01-2021&createdEnd=01-06-2021&RawJsonResponse=true
🧾 Step 7: Add API Tokens to the Request Headers
In the Power BI request:
Add Token1 and Token2 from Step 3 as header parameters.
Then click OK to establish the connection.
📊 Step 8: Load and Format Your Data
Power BI will parse the response and load it as a table.
Click Close & Apply to finish loading.
You can now start building and formatting Power BI reports using your Layer data!
API application tokens will automatically be created in the Settings - Token Management area (if they are not select ‘refresh tokens’ against the application and they should appear).
Note: Tokens are valid for 1 year.
Ensure the user you are using to access the API has the ‘API – Access All Areas’ Developer API option selected in Settings – Users – Roles.
Select the URL for the API you want to connect with, eg. https://webapi.thelayer.com/api/Opportunity/ByTenant?createdStart=01/01/2021&createdEnd=17/06/2021
Note: You may need to add parameters to the URL such as showOnlyOpen, createdStart and createdEnd dates as in this example.
[Layer API details are available from https://webapi.thelayer.com/swagger/ui/index].
Open Power BI desktop, create a new file and select ‘Get data – Web’ to connect to the Layer API.
Choose ‘Advanced and then enter your API URL in the URL parts field.
Due to the way Power BI parses this data you need to add an extra parameter onto the query string of this URL before it's ready to go; RawJsonResponse=true
So our final API URI would be:
Copy both Token1 and Token 2 (from step 3) into the form request header parameters and select ‘Ok’.
Once the connection has been established the data will be loaded and automatically converted into a table.
Select ‘Close & apply’ from the menu to finish loading the data.
You can now start formatting the data into Power BI reports.