API iteration and scheduled refresh

[abhigna]

Hi,

I have very basic understanding of M and I use data from an API for my report and this requires Iteration. The report refreshes on desktop but I  see an error " Some data sources may not be listed because of hand-authored queries" in data source settings and on report server the data sources option is greyed out and I cannot schedule a refresh.

The code snippet for the API call is

let

FnGetOnePage =
(url1) as record =>
let
Source = Json.Document(Web.Contents(url1, [Headers=[Accept="application/json", #"Content-        Type"="application/json", Authorization="Bearer "&APIToken],Content=Text.ToBinary(Body)])),
data = try Source{0}[results] otherwise null,
next = try Source{0}[next] otherwise null,
next_offset = try Source{0}[next_offset] otherwise null,
res = [Data=data, Next=next, Offset=next_offset]
in
res,

GeneratedList =
List.Generate(
()=>[res = FnGetOnePage(URL&"?limit="&RowLimit)],
each [res][Data]<>null,
each [res = FnGetOnePage([res][Next])],
each [res][Data]),

CombinedList = List.Combine(GeneratedList),

I tried a couple of fixes without success. Is there a workaround for this?

Thank you!

[Daryl-Lynch-Bzy]

Hi @abhigna -

First - I think you can improve your code by understand how to include Relative Path to capture "?limit=XX" part of the URL.  Could you please read the following post by Chris Webb to understand this concept? 

Chris Webb's BI Blog: Handling Multiple URL Query Parameters With The Same Name Using Web.Contents I...

Second - Your List Generate does not appear to reference Row Start.   You have included RowLimit number.  I think you might be missing RowStart?  i.e. the limit always equals 100, but the row start counts up 1, 101, 201.  

Please look at the following from Gorllia BI -How to use List.Generate to make API Calls in Power Query M - BI Gorilla