Using Rows from OData Query to Run and Merge Another OData Query

[Anonymous]

Hi all,

Apologies if this has already been answered - I was only able to find single-column iterative/looping examples, not entire query results - but I'm basically looking to use a prinary key from one OData query ("Companies") to then run and merge another query ("GL Entries").

The query that pulls the list of companies:

let
    Source = OData.Feed(http://www.api.abc.com/companylist, null, [Implementation="2.0"]),
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.Contains([Name], "Test"))
in
    #"Filtered Rows"

The query that runs and gets the entries:

(CompanyFilter as text) =>
let
//Variables
    BaseURL = "https://www.api.com/",
    InstanceID = "123456-abcdef-7483743",
    QueryName = "GLEntries",
    CompanyParameter = "/?company=",
    ConcatenatedURL = BaseURL & InstanceID & "/" & QueryName & "/" & CompanyParameter & CompanyFilter,
//Source
    Source = OData.Feed(ConcatenatedURL, null, [Implementation="2.0"]),
    #"Added Custom" = Table.AddColumn(Source, "CompanyID", each CompanyFilter)
in
    #"Added Custom"

How would I go about passing the company name value from the first query as a parameter into the second query and then merging all the results?

[v-yuta-msft · Accepted solution]

@Anonymous ,

You can create a custom function using the first query and then invoke the query in the second function. About how to create custom function, you can refer to doc and blogs below.

https://radacad.com/writing-custom-functions-in-power-query-m

https://blogs.msdn.microsoft.com/mvpawardprogram/2013/08/19/creating-power-query-functions/

https://www.poweredsolutions.co/2019/02/19/parameters-and-functions-in-power-bi-power-query-custom-functions/

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you -- I suppose I was trying to overengineer it by using loops and other iterative functions when it really is as easy as adding a custom invoked-function column.