Run For Each Entity in List in API Call (Sage200)

[Kureigu]

Hi,

I've been racking my brain with this issue for a while, my knowledge with Power Query is relatively limited, at least when it comes to M code - so apologies in advance for my poor terminology and understanding.

I'm trying to run a query to pull data from Sage 200's API, using the base template file provided by them (the resources and documentation of which can be found here, assuming sharing the link is allowed). Each query runs the query 'API Query', which takes the 'endPoint', 'filter' and 'parameters' from the actual query, and 'API Query' calls 'Feed' which ultimately tells the queries which company to grab data from via the API.

My issue is we have multiple companies and we could do with adding all the companies in a single query, to save clutter and having to set each one up individually (since we're also adding a unique identifier to each company), but because each query/function doesn't really tell me much my only assumption is that there's additional steps hidden somewhere that I can't find for the life of me, and even if I could find them the likihood I could get it to do what I want is likely very low.

My current set up is a bunch of queries for each company, an example of which is below.

Top level query:

let
    Source = #"API Query"("customer_views",null,"$select=id,reference,name,balance,account_type,credit_limit,average_time_to_pay,value_of_current_orders_in_sop,customer_location_full_address,analysis_code_1"),
    #"Added Custom" = Table.AddColumn(Source, "Entity", each @#"Company Name")
in
    #"Added Custom"

then the 'API Query', which has 3 parameters somehow (endPoint, filter, parameters):

let
    Source = #"Feed (1)"(#"Company Name")
in
    Source

 then 'Feed', which has the 'companyName' parameter:

let
    Source = Sage200.Feed()
in
    Source

I essentially need to somehow tell 'Feed' to loop through a list of company names for each query (currently in a list called 'Companies', which I believe means I need to somehow combine all 3 (which is fine, I'd rather do that than have to build a query for each company and combine/append them all), but I haven't a clue how since it seems that a lot of the 'code' is missing or at least it's very different to how I'd expect it to be (there's no actual written API call or API key etc anywhere).

If anyone has any knowledge around this already or perhaps could help or steer me in the right direction it'd be greatly appreciated, my current assumption is I need to start from scratch and write it all myself, but I'm hoping not as a simple change of each 'API Query' parameter is also easier than having to rebuild entire queries from scratch.

Thanks in advance,

CJ

[lbendlin]

There is no included connector "Sage200" in Power Query.  Is that a custom connector?

[Kureigu]

Good question. There's a 'Sage 200Cloud' connector, which when using it appears to generate a query that's very similar to the 'Feed' query, but I've no idea how it's actually sourcing the information for that query as I've not provided anything personally and the data source is local/SQL (via API).

The data source settings are below, whatever the 'Sage200' source is, it can't be exported.

Edit: I've just noticed the 'Sage 200Cloud' source isn't available on my personal install of Power BI, so I guess that answers the question - it must be a custom connector that's integrated into the file somehow. No idea how to configure it though, or how it has been embedded.