Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
abhigna
New Member

API iteration and scheduled refresh

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!

1 REPLY 1
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors