How to retrieve all data from a Paginated API to pbi report

[Anonymous]

Hi Team,

Im facing a problem to retrieve all the data from a paginated API,is there any way?

My API is look like....

http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/{rowCount}/{pageNumber}

http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/10/1

Thanks in advance!

[danextian]

Hi @Anonymous,

This can involve creating a list of URLs in M and then using this URL as the webpages containing the data tables or creating a custom function.

The code below createst a list of possible urls based on row and page number combinations from 1 to 100. The large the value you have, the longer it will take for the code to finish executing as this involves looping through each webpage (they're being opened in the background).

let
    /*create two columns containing numbers from 1 to 100 representing the Row and Page numbers,
    replace these with your desired numbers */
    Source = Table.FromColumns({{1..100}, {1..100}}),
    
    //rename the generated columns
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Row"}, {"Column2", "Page"}}),

    //generate the web urls and fetch the data table,
    //the 0 in {0}[Data] represents table 1 of the webpage
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each try  
        let 
            url = Web.Contents( "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]),
            [ManualStatusHandling={404}]), //check if webpage returns 404 error
            GetMetadata = Value.Metadata(url),
            GetResponseStatus = GetMetadata[Response.Status]
            in if GetResponseStatus=404 then null else url{0}[Data] //returns null if GetResponseStatus is 404
            otherwise null, type table) //try...otherwise - if this step returns an error, value is null
    
in
    #"Added Custom"

[Anonymous]

@danextian,

This is just retures null only.

[danextian]

Hi @Anonymous

http://accountapidev.xxxx:2000/

Is this is an actual website? The M script will return null if

• http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]) returns a 404 error
• http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/ is an invalid URL resulting to an error

Since accountapidev.xxxx is an invalid URL (at least on my end as I don't have access to it), the result is an error so the script returns null. 

[Anonymous]

@danextian,

Thats not the actual URL,the image attached is the result of real URL.

Thanks.

[danextian · Accepted solution]

Hi @Anonymous

Please try this modified code.  I remove the part that returns null if url is invalid. 

let
    /*create two columns containing numbers from 1 to 100 representing the Row and Page numbers,
    replace these with your desired numbers */
    Source = Table.FromColumns({{1..100}, {1..100}}),
    
    //rename the generated columns
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Row"}, {"Column2", "Page"}}),

    //generate the web urls and fetch the data table,
    //the 0 in {0}[Data] represents table 1 of the webpage
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each  
        let 
            url = Web.Contents( "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]),
            [ManualStatusHandling={404}]), //check if webpage returns 404 error
            GetMetadata = Value.Metadata(url),
            GetResponseStatus = GetMetadata[Response.Status]
            in if GetResponseStatus=404 then null else url{0}[Data] //returns null if GetResponseStatus is 404
            , type table)     
in
    #"Added Custom"

Please post a screenshot of the error similar with below:

[Anonymous]

@danextian,

Thank you for the quick reply...Please see the attached image.Its showing records but its having error message only.

[danextian]

Hi @Anonymous,

I realized I did not include Web.Page function.

Please try this (does not capture whether a URL is invalid).

let
    /*create two columns containing numbers from 1 to 100 representing the Row and Page numbers,
    replace these with your desired numbers */
    Source = Table.FromColumns({{1..100}, {1..100}}),
    
    //rename the generated columns
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Row"}, {"Column2", "Page"}}),

    //generate the web urls and fetch the data table,
    //the 0 in {0}[Data] represents table 1 of the webpage
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each  
        let 
            url = Web.Page( Web.Contents( "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]),
            [ManualStatusHandling={404}]) ), //check if webpage returns 404 error
            GetMetadata = Value.Metadata(url),
            GetResponseStatus = GetMetadata[Response.Status]
            in if GetResponseStatus=404 then null else url{0}[Data] //returns null if GetResponseStatus is 404
            , type table)     
in
    #"Added Custom"

[danextian]

Hi @Anonymous

Please try this modified code.  I remove the part that returns null if url is invalid. 

let
    /*create two columns containing numbers from 1 to 100 representing the Row and Page numbers,
    replace these with your desired numbers */
    Source = Table.FromColumns({{1..100}, {1..100}}),
    
    //rename the generated columns
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Column1", "Row"}, {"Column2", "Page"}}),

    //generate the web urls and fetch the data table,
    //the 0 in {0}[Data] represents table 1 of the webpage
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each  
        let 
            url = Web.Contents( "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/" & Number.ToText([Row]) & "/" & Number.ToText([Page]),
            [ManualStatusHandling={404}]), //check if webpage returns 404 error
            GetMetadata = Value.Metadata(url),
            GetResponseStatus = GetMetadata[Response.Status]
            in if GetResponseStatus=404 then null else url{0}[Data] //returns null if GetResponseStatus is 404
            , type table)     
in
    #"Added Custom"

Please post a screenshot of the error similar below:

[Anonymous]

You can do this with a function.

I cant provide an exact method without seeing more information. However I will provide a short guide.

If you create a normal query for page 1. Then in your query replace the page number with a parameter, then right click on this query click create function.

Next Iwould create a table with a column containing all pages that you want, then run this function taking the page number column as the argument. This will create a new column with tables. Then combine all these tables to give you the combined data.

[Anonymous]

Hi @Anonymous,

Thank you for the quick reply!

Could you share the sample code? for the page 1 i need the following URL,that will return 10 rows in a page.

http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/10/1

[Anonymous]

As I do not have a paginated API to demo with I cant provide any code. But I will try explain it clearer

When you make a query for the first page you are defining the url that the API should query http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/10/1.

What you need to do is create a parameter, set it to a text data type and the default value to 1.

THen in the advanced editor where the URL in the source is written as "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/10/1" change it to "http://accountapidev.xxxx:2000/api/cola/GetOrderHistory/10/" & Parameter1 (If you have renamed the parameter put its name here instead)

If you execute this change, nothing should change your result should look the same.

Then right click on the query in the list of queries on the left hand side and click on create function.

This will create a function that will take an input as where you had your parameter and output the final result of the query, in this case a table.

Then you want to apply that query to the column of page numbers, in this case you could just enter the numbers 1 to 10 in the enter data window.

Then under the add column tab click invoke custom column then select your function with the page number column as the parameter N.B. you might run into an issue where the function was expecting a text value yet your column will be automatically typed to numbers, in this case just change the type of the column to text before applying the function.

Then you should have a column with a bunch of tables in it, you can then expand that column and it will append all of the tables together.

Note

This method is assuming that each of the pages is the same, if the pages are different the best way is to write a query for each page, make them the same structure then append.