Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Activenet API

Has anyone successfully connected to get data from Activenet using API? I could use some help and directions here.

1 ACCEPTED 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

5 REPLIES 5
v-shex-msft
Community Support
Community Support

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

ACTIVITY SEARCH API V2 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

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"

headerdata.jpg

 
 
 
 

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.