Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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!
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |