Hubspot refresh not working in Services ( Not using connector, Using Private App Oauth2.0)

[shivendr]

Hi ,
I am connected with Hubspot using Private app and Oauth2.0.
for connection I have written Mcode in a blank query like :

let
apiUrl = "https://api.hubapi.com/crm/v3/properties/deals",
apiKey = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX",
headers = [
#"Authorization" = "Bearer " & apiKey,
#"Content-Type" = "application/json"
],
options = [
Headers = headers
],
allData = List.Generate(
() => [url = apiUrl, options = options, page = 1],
each [page] <> null,
each [
url = apiUrl & "?limit=100&page=" & Text.From([page]),
options = options,
page = [page] + 1
],
each
let
response = Web.Contents([url], [options]),
json = Json.Document(response),
results = json[results]
in
if List.IsEmpty(results) then null else results
),
records = List.Combine(allData),
table = Table.FromRecords(records)
in
table

I am connected with the data in Power BI desktop but when I published the report to power bi services, It's giving error for refesh.


If anyone could help me in solving the refresh issue in services or any workaround to solve the issue, highly appreciated.

Thanks! in advance

[QueryWhiz]

Hi @shivendr As a workaround, maybe you can use a 3rd party connector, which pulls data directly from the Hubstpot API. Once you report is ready, you can publish it on PBI service and it should refresh fine.  I've tried windsor.ai, supermetrics and funnel.io. I stayed with windsor because it is much cheaper so just to let you know other options. In case you wonder, to make the connection first search for the Hubspot connector in the data sources list:

After that, just grant access to your Hubspot account using your credentials, then on preview and destination page you will see a preview of your Hubspot fields:

There just select the fields you need. Finally, just select PBI as your data destination and finally just copy and paste the url on PBI --> Get Data --> Web --> Paste the url. 

[DrewSmith]

Hi, did you get this working? I've the same issue and tried everything I can think of. 

[Anonymous]

Hi @shivendr ,

Based on the screentshot you provided, it shows that you can't set up a schedule refresh this kind of data source.

This issue happens when the data source you used, within Power BI Desktop, isn't supported for refresh. You need to find the data source that you're using and compare that against the list of supported data sources at Refresh data in Power BI.

Official document about troubleshooting this issue:

Troubleshooting unsupported data source for refresh - Power BI | Microsoft Learn

In most cases, Power BI datasets that use dynamic data sources can't be refreshed in the Power BI service. There are a few exceptions in which dynamic data sources can be refreshed in the Power BI service, such as when using the RelativePath and Query options with the Web.Contents M function. Queries that reference Power Query parameters can also be refreshed.

What you can do as a workaround is to use the relative path option as shown below

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...

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.