Forum Discussion
Activenet API
Hi Anonymous ,
Maybe you can try to use the following custom function to iterator all pages and combine then to one:
let looping=(rootpath as text,apikey as text,QueryString as text, optional tb as table)=> let //QueryString="usage_date=2020-01-01",//query string filters and parameters //apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document //rootpath="http://api.amp.active.com", Source=Web.Contents(rootpath, [RelativePath="/api/v1/membershipusages?"&QueryString&"&api_key="&apikey] ), page_info = Source[headers][page_info], cPage=Table.SelectRows(page_info,each [Name]="page_number"){0}[Value], tPage=Table.SelectRows(page_info,each [Name]="total_page"){0}[Value], Result= if cPage<=tPage then if tb <>null then @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Table.Combine({tb,Json.Document(Source)})) else @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Json.Document(Source)) else tb in Result in loopingNotice: I'm not so clear for the response table data structure, so you need to do some custom on 'Table.Combine' and 'Json.Document' functions to extract needed fields and records.
Regards,
Xiaoxin Sheng
I tried following both the options- getting error message - "method not allowed".
Here's how far I get when I try to do it on my own. Entering the url as source returns a table with header and body.
Expanding the header returns a table with page info.
let
Source = Json.Document(Web.Contents("https://api.amp.active.com/anet-systemapi/****/api/v1/membershipusages?usage_date=2020-01-01&api_key=****")),
headers = Source[headers],
page_info = headers[page_info],
#"Converted to Table" = Record.ToTable(page_info)
in
#"Converted to Table"
On clicking the body, I get the actual data. How do I iterate the info from header to get the data?
The odata method didn't work coz active doesn't use odata, I guess.
let
Source = Json.Document(Web.Contents("https://api.amp.active.com/anet-systemapi/****/api/v1/membershipusages?usage_date=2020-01-01&api_key=****")),
body = Source[body],
#"Converted to Table" = Table.FromList(body, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"customer_id", "customer_first_name", "customer_last_name"}, {"customer_id", "customer_first_name", "customer_last_name"})
in
#"Expanded Column1"
Hi Anonymous ,
Maybe you can try to use the following custom function to iterator all pages and combine then to one:
let
looping=(rootpath as text,apikey as text,QueryString as text, optional tb as table)=>
let
//QueryString="usage_date=2020-01-01",//query string filters and parameters
//apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
//rootpath="http://api.amp.active.com",
Source=Web.Contents(rootpath,
[RelativePath="/api/v1/membershipusages?"&QueryString&"&api_key="&apikey]
),
page_info = Source[headers][page_info],
cPage=Table.SelectRows(page_info,each [Name]="page_number"){0}[Value],
tPage=Table.SelectRows(page_info,each [Name]="total_page"){0}[Value],
Result=
if cPage<=tPage
then
if tb <>null
then @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Table.Combine({tb,Json.Document(Source)}))
else @looping(rootpath,apikey,QueryString&"&page_number="&Text.From(cPage+1),Json.Document(Source))
else tb
in
Result
in
looping
Notice: I'm not so clear for the response table data structure, so you need to do some custom on 'Table.Combine' and 'Json.Document' functions to extract needed fields and records.
Regards,
Xiaoxin Sheng