Refresh unsupported source Alternatives

[Anonymous]

There's been a lot of threads about the dreaded "Unable to refresh the model because it references an unsupported data source" error. I have a query that uses a column in a table as a parameter to be ran inside an API call so Power BI service won't let me refresh.

I saw some suggestions to define a Relative Path in the URL but haven't been successful in making it work.

I'm wondering if I went through the Data Gateway if it might bypass the issue? What about if I built the model inside Azure Analysis Services?

[Anonymous · Accepted solution]

I ended up solving the problem by following this blog post and tweaking it 

https://blog.crossjoin.co.uk/2016/08/16/using-the-relativepath-and-query-options-with-web-contents-i...

Then I connected it along all my other sources to the Power BI Data Gateway and everything seems to work now!

[v-lid-msft]

Hi @Anonymous ,

Glad to hear that you have resolved your problem. Thank you for sharing this wonderful solution, we have learned a lot from it.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Hi there

Without more specific details it might be tough to fully understand if the data source would work with AAS

Have you tried to connect to the data source via an ODBC connection?

[Anonymous]

The source is a data source with a web url api. 

I have to call on it in a way similar to this "www.datasource.com/api/KPI1&KPI2&KPI3/Project=_______"

I then have a list of project names as a separate source (sharepoint file) and a column is added to that table which runs the column ProjectName against inside that URL.

Because of that query which requires dynamic parameters, when I refresh the data on Desktop, there's no problem. However when I publish and then run a refresh of the data set from the service, I get "Unable to refresh the model (id=10889070) because it references an unsupported data source."

I know that function isn't possible with Power BI yet so I'm trying to see if running either a Data Gateway or AAS model would solve the issue.

Regarding the ODBC connection, I don't think that applies here given the API call being a web url.

[v-lid-msft]

Hi @Anonymous ,

Could you please try to connect to single project id of this url api in another report and try to refresh in the Power BI Service?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-lid-msft I just tried creating a static table with a single project name and Invoking Custom Function as a separate column. I published it and get the exact same error "Data source errorUnable to refresh the model because it references an unsupported data source."

[v-lid-msft]

Hi @Anonymous ,

Based on this document: https://docs.microsoft.com/en-us/power-bi/refresh-data#review-data-infrastructure-dependencies

Power BI currently does not support parameterized data source definitions, also known as dynamic data sources. For example, you can't parameterize the data access function Sql.Database("SqlServer01", "AdventureWorks"). If your dataset relies on dynamic data sources, Power BI informs you that it detected unknown or unsupported data sources. You must replace the parameters in your data access functions with static values if you want Power BI to be able to identify and connect to the data sources.

Are you using the web connector to connect the web api?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[GilbertQ]

Which data source type are you using to connect to the data?

[Anonymous]

@GilbertQ 

It's two fold.

The first is using a Web URL (Web connector) which requires a parameter in it. I defined that parameter as "= (ProjectName as text) =>" at the top of the query to turn it into a custom function.

I then use an Excel table hosted on Sharepoint which has a column called ProjectName. I added a colum with the "Invoke Custom Function" option and chose that column (ProjectName) as the parameter by which to run every row against that Custom Function.

Whenever I do a refresh within PBI Desktop, everything works like a charm.