Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Has anyone successfully connected to get data from Activenet using API? I could use some help and directions here.
Solved! Go to Solution.
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
Hi @Anonymous ,
You can try to use following query formula if they suitable for your requirement:
let
QueryString="query=running&category=event&start_date=2013-07-04..&near=San%20Diego,CA,US&radius=50",//query string filters and parameters
apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
Source=Web.Contents("http://api.amp.active.com",
[RelativePath="/v2/search?"&QueryString&"&kids=true&api_key="&apikey]
),
Result=Json.Document(Source)
in
Result
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft . This is the first step. In my scenario, the response header returns total_results, items_per_page, total_pages. How do I extract this information from the header and put it in an iteration to get all the data?
Additionally, is there a way to increase items_per_page to reduce the number of calls?
Hi @Anonymous ,
Maybe you can try to add them into the content parameter with detail API response settings:
Specifying JSON Query in Power Query – Example Statistics Sweden
let
QueryString="query=running&category=event&start_date=2013-07-04..&near=San%20Diego,CA,US&radius=50",//query string filters and parameters
apikey="sqq35zvx6a8rgmxhy9csm8qj",//apikey used to verify api, this is a sample key from document
bodypara="{}",//total_results,items_per_page,total_pages
Source=Web.Contents("http://api.amp.active.com",
[RelativePath="/v2/search?"&QueryString&"&kids=true&api_key="&apikey,
Content = Text.ToBinary(bodypara)]
),
Result=Json.Document(Source)
in
Result
For getting total records from pagination rest API, you can refer to the following blog:
How To Do Pagination In Power Query
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |