REST API Dynamic Datasource aren't refreshed

[LPAN012345]

Hi,

I have a query that gets data from a REST API including an order number column.  I've then created a function in PowerQuery that you pass the order number to as a parameter and it calls a second API with the order number as part of the URL to return who created the order.

This works perfectly within the desktop designer, but when published I get a "This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service"!?

Is there a way around this or, is there a way to call an API from a DAX command instead of Query?

Thanks in advance

Lee.

[LPAN012345 · Accepted solution]

Thanks guys, the "relative path" parameter did help me get this issue fixed.  Although I still had further issues with the query.

For anyone who has a similar issue then do this:

1 - Add the Relativepath to the web.contents as described above.  To ensure it uses the same credentials as the original API call, make sure the URL part of the call is the entire URL up to where the parameters start (normally the ? character) and then use the relative path for the parameters (the rest of the URL)

2 - Make sure you make the 2nd API call as soon as possible within the Query ideally after the "Table.ExpandRecordColumn"

3 - Any further changes should be split out into a new query that then has the source of the query above.

If you follow these rules, it should work!

Lee.

[PowerBI_Zeus]

Hi @LPAN012345, could you please share you code. Don't get points 2 and 3 quiet well. 
I am currently stucked on this right now.

[Anonymous]

@LPAN012345 

You may refer to the following blog to set schedule refresh on dynamic data source, and the second link is Chris Webb's RelativePath article.

Setting a scheduled refresh on a Dynamic Data Source in Power BI | by dataninja | DataDrivenInvestor

Chris Webb's BI Blog: Using The RelativePath And Query Options With Web.Contents() In Power Query An...

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[blopez11]

Web APIs are a little finicky between the Desktop and the Service (i.e. what works in the Desktop doesn't always work in the Service)

In the service, you cannot have the url parameter (first parameter) to Web.Contents be dynamic

The url parameter should be the base url of your web service api, then use the RelativePath and Query options to dynamically extend the URL

See below for additional details on the options to Web.Contents

https://docs.microsoft.com/en-us/powerquery-m/web-contents

There are also several blogs that have examples

However, even after doing all of this you may be bit by other issues, like authentication