Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Need help for Paging power query based on offset and limit

Hi,

I am using below url for table count which can give table limit:

https://xxx.com/api/now/v1/stats/u_tap_request?sysparm_count=true

 

and using below url to fetch the data:

https://xxx.com/api/now/table/u_tap_request?sysparm_display_value=false&sysparm_exclude_reference_link=true&sysparm_offset=1&sysparm_limit=100

where we can put sysparm_offset=1 and sysparm_limit=100 -  these 2 parameter for offset and limit value.

 

Now I have made 1 function to dynamically set the offset value:

(Offset as number) as table =>
let
Source = Json.Document(Web.Contents("https://xxx.com/api/now/table/u_tap_request?sysparm_display_value=false&sysparm_exclude_reference_link=true&sysparm_offset=" & Number.ToText(Offset) & "&sysparm_limit=100")),
#"Converted to Table" = Record.ToTable(Source),
#"Expanded Value" = Table.ExpandListColumn(#"Converted to Table", "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"parent", "u_solution_complexity_date_time"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Name"})
in
#"Removed Columns"

 

now i want to load the whole table dynamically with each iteration 100 rows and have to make a table. So how can achieve that?

Need Help

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi,

      I am really stuck here and dont know the code or logic how to iterate the table dynamically based on the total records. Please help me on that.

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        Hi Anonymous ,

        please check this sample query. It should give you some ideas how to tackle your task:

         

        let
    Source = List.Generate( ()=>
[Result = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/")), Counter=0, Next = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/"))[next]],
each [Next] <> null,
each [
Next = [Result][next],
Result = let
        next = [Result][next],
        offset = Text.BetweenDelimiters( next, "offset=", "&"),
        limit = Text.AfterDelimiter(next, "limit="),
        StartWithEmptyTable = Json.Document(Web.Contents("https://pokeapi.co/api/v2/ability/?offset=0&limit=20", [Query=[offset=Text.From(offset), limit=Text.From(limit)]]))
    in
        StartWithEmptyTable,
Counter = [Counter] + 1

]
),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Result", "Counter"}, {"Result", "Counter"}),
    #"Expanded Result" = Table.ExpandRecordColumn(#"Expanded Column1", "Result", {"count", "next", "previous", "results"}, {"count", "next", "previous", "results"}),
    #"Expanded results" = Table.ExpandListColumn(#"Expanded Result", "results"),
    #"Expanded results1" = Table.ExpandRecordColumn(#"Expanded results", "results", {"name", "url"}, {"name", "url"})
in
    #"Expanded results1"