how to create a M query ( Power Query SDK) to Pull all Page Record

[Anonymous]

Hi ,

I would like to connect API using custome connector to pull the all page data in singble table.

API  I able to pull only 100 record. If I would like to pull remain record using paging . What function need to use . ANy guidance please 

Below Query give me the URL for pulling the1000 record, How to write the condiation to pull all the page record in single page.

[Anonymous]

Hi ,

With above code I can fetch 1000 records of data. For " Remdesivir " total record count is "7239" with limit able to fetch 1000 record without any issue.

Same problem, I have with custome connector . My requirement is fetch all the data without any limit. Please help me same.

[lbendlin]

My sample code fetches all records. Note that Power Query by default only shows 1000 preview rows. Click "Close and apply" to get everything.

[Anonymous]

Thanks for update.

Yes, We are able to pull the records. Is there any possible make use of same code in Custome Connector. 
Any advice one same. Requirement is achieve with Custom Connector.

[lbendlin]

Yes, you can copy that code into the custom connector Section1. Then add the required code for connection test etc. Follow the documentation for custom connector creation.

[Anonymous]

Hi,

Work perfectly . By the time the record pull more than 26k the query get fail.

Query1
OLE DB or ODBC error: [DataSource.Error] Web.Contents failed to get contents from 'https://xxxxx/event.json?search=%20&' (400): Bad Request.

Please help me.

[lbendlin]

What's the total number of rows for that query?  Maybe you ran out of data? Maybe they refreshed the data while you were pulling it?

[Anonymous]

Hi,

 When it hit max 26k we are getting the error. That's limit setup for API. 

[lbendlin]

The link you provided lists a number of good alternatives. Decide on one of them and implement it.

[Anonymous]

Hi Sir,

Please help me how to implement search_after function . I am new to M query . 

[lbendlin]

This particular API doesn't have a nextlink navigator, it uses Limit and Offset (skip). Therefore it is better to use List.Generate. Below is a sample implementation.

let
    Search = "remdesivir",
    Limit = 100,
    Rows = Json.Document(Web.Contents("https://api.fda.gov/drug/event.json",[Query = [search = Search]]))[meta][results][total],
    Fetch = List.Generate(()=>0, each _ < Rows, each _ + Limit),
    #"Converted to Table" = Table.FromList(Fetch, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Custom", each Json.Document(Web.Contents("https://api.fda.gov/drug/event.json",[Query = [search = Search, limit = Text.From(Limit), skip = Text.From([Column1])]]))[results]),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".