How to Connect Power BI with an API

Can you connect an API to Power BI?

Yes! You can connect to (most) API's! Okay, how can we accomplish this?

1. Choose Get Source

2. Choose Web

3. Enter the URI

4. Use the relative path options argument for more advanced capabilities

---

1. Choose Get Source

The first step is to choose a data source. Similar to connecting to any other source like a CSV or Excel file.

2. Choose Web

Now, you will just select "Web".

3. Enter the URI

This will usually contain an access token and any filter arguments that the API takes.

4. Use the relative path for more advanced capabilities

“You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh.”

1. If you want to set up a refresh schedule in service, you'll need to make some modifications in Power Query.

2. Open up Power Query, navigate to the query that makes the API call. Now, open the Advanced Editor.

3. In the Web.Contents function, we need to use the options argument and give it a relative path.

webContents = Web.Contents(
   "https://api.stlouisfed.org/",
   [
   RelativePath = 
      "fred/series/observations?series_id=GDP&api_key=" & api_key
   ]
)

---

Because a relative path is technically a dynamic data source, Power BI requires us to define this in the relative path argument if we want to refresh in the service.

---

Still won't refresh? Try this other option:

1. Open your dashboard on desktop

2. File -> Options -> Privacy

3. Choose "Always combine"

4. Publish

Still still won't refresh? Try this other option:

1. In desktop, change privacy settings for the data source to 'Organizational'

2. Publish

3. Navigate to 'Schedule Refresh' in the service

4. Expand Data source credentials

5. Click 'Edit credentials' for the API source.

6. Change the privacy level to to 'Organizational'

Still still still won't refresh? Try this other option:

"Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again."

1. You can try using a gateway and choose 'Skip Test Connection' as described in this post: https://community.powerbi.com/t5/Service/Refreshing-data-from-REST-API-works-on-Desktop-fails-on-Service/td-p/779926

2. To set up a gateway involves a number of steps outside the scope of this article, but you can read how to do that here: https://learn.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

Still still still still won't refresh?! Try this other option:

"This dataset contains a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this data source won't be refreshed."

1. Unfortunately you may be out of luck if you have this error. You'll need to rebuild your query so that Power Query is not using the dynamic data source, if at all possible.
   

1. Dynamic data sources are strings generated within the M code like database names, CSV file paths, or web URLs.

2. The only exception to this rule is to use the 'RelativePath' function described above in this article.

3. Read more about dynamic data sources here: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data