Forum Discussion

jtpiazzamn's avatar
jtpiazzamn
Helper I
2 years ago

Power BI rest API pagination error in query

I'm trying to figure out why pagination is not working - I copied this from another environment. Its getting stopped in the page #

 

In Power Query I have the following setup

 

 

 

API Query 

========================

= (URL as text, Object as text, Parameters as text) => let
Query = "https://website.com/api/v4/" & Object & "/?limit=1000&" & Parameters,

pageCount = () =>
let
Source = Json.Document(Web.Contents("https://website.com/api/v4/", [RelativePath= Object & "/?limit=1000&"])),
Meta = Source[meta],
pageCount = Meta[numberOfPages]
in
pageCount,

getPage = (Index) =>
let
Index = Number.ToText(Index),
Page = Json.Document(Web.Contents("https://website.com/api/v4/", [RelativePath= Object & "/?limit=1000&" & Parameters & "&page=" & Index]))
in
Page,

PageIndices = { 1 .. pageCount() },
Pages = List.Buffer(List.Transform(PageIndices, each getPage(_))),
Table = Table.FromList(Pages, Splitter.SplitByNothing(), {"raw"}),

ExTable = Table.ExpandRecordColumn(Table, "raw", {"data"}, {Object})
in
ExTable

in
Source

 

==========================================

URL1

= "website.com" meta [IsParameterQuery = true, IsParameterQueryRequired = true, Type = type text]

 

======================================

In each source table I'm downloading starts with: (the table name in this case is "suggestion"

= Json.Document(Web.Contents("https://website.com/api/v4/suggestion/?limit=1000&page=1&include=,id,projectId,userid,projectName,authorEmail,created,status,phase,title,content&filter=deleted:false"))

Need Help

2 Replies

    • jtpiazzamn's avatar
      jtpiazzamn
      Helper I
      2 years ago

      Thank you for your response.

      The one that seemed like it would work for me is the Facebook example (the site medium.com site won't work because my site doesn't support $stop etc). However, I don't know how to incorporate the page number into the URL using the facebook example. 

      the format for my API has the page # within the URL ie:
      https://website.com/api/v4/?limit=1000&page=1&include=id,name

      ========================

      let
      fbPage = "MicrosoftBI", // You can type your facebook page here
      iterations = 10, // Number of iterations
      url =
      fbPage &
      "/posts?limit=100&fields=message,created_time" &
      "&access_token=[<strong>Your access token here</strong>]",
       
      // FnGetPage is the function that performs an import of single page.
      // The page consists of a record with the data and the URL in the
      // fields data and next. Other Web APIs hold the data and cursor in different formats
      // but the principle is the same.
      FnGetOnePage =
      (url) as record =>
      let
      Source = Json.Document(Web.Contents(url)),
      data = try Source[data] otherwise null,
      next = try Source[paging][next] otherwise null,
      res = [Data=data, Next=next]
      in
      res,
       
      GeneratedList =
      List.Generate(
      ()=>[i=0, res = FnGetOnePage(url)],
      each [i]<iterations and [res][Data]<>null,
      each [i=[i]+1, res = FnGetOnePage([res][Next])],
      each [res][Data]),
       
      #"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      #"Expanded Column2" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
      #"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1", {"message", "created_time", "id"}, {"message", "created_time", "id"})
      in
      #"Expanded Column3"