Help in configuring API call in Power Query for refresh in Power BI services

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

I need to configure the above M-code for refresh in Power BI services.

the error which i am getting is :


Thanks in advance!

[ReportRanger]

Hi @shivendr As a workaround, maybe you can use a 3rd party connector, which pulls data directly from the Hubspot API, it can save you a lot of time compared to other options. 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. 

[Anonymous]

Hi @shivendr ,

Please see the solution posted under this thread:
Solved: Re: Not Able to Refresh the Dataset :You Can’t sch...
Web.Contents(), M Functions And Dataset Refresh Errors In Power BI 
Please try 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("https://api.hubapi.com", [RelativePath="/crm/v3/properties/deals?limit=100&page=" & Text.From([page]), Headers=headers]),
                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

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[shivendr]

Hi Gao,
I tried your M-code but It's giving duplicates of first100 rows and iterating upto infinite. which is very difficult to handle.
I limited the rows to 1000 and removed duplicates and found that it is having only 100 rows whereas in actual data we have 6000 rows.

[shivendr]

From the source , I am getting this type of URL at the end of first 100 rows : 

    "paging": {

        "next": {

            "after": "5852",

            "link": "https://api.hubapi.com/crm/v3/objects/CONTACT/?limit=100&after=5852"
In My openion, Its a issue of paging, I am not able to code this in M-code to get the value.

[rubayatyasmin]

Hi, @shivendr 

make sure data privacy level is set to Organizational