Forum Discussion
Help in configuring API call in Power Query for refresh in Power BI services
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
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.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.
