Query to append data from the same table in 500 Dataverse database instances

[9427592754]

Hi all,

I realize that this may not be possible in a single query per table, but before I jump on building an expensive solution I thought of asking here first if there is an easy/smart way of doing this.

I have 500 Dataverse database instances and in each instance I have the same schema - the same table structure. So in each database instance I have table DRINKS, table FOOD, table COSMETICS and so on.

I must get the data from tables DRINKS, FOOD, COSMETICS from each instance. Ideally I would want to build 1 query per table, rather than 500 queries or having to build 500 Azure Synapse links. Below I am providing the query per database per table.

let
	Source = Cds.Entities("https://instance_1", null),
    entities = Source{[Group="entities"]}[Data],
    pub_drinks = entities{[EntitySetName="pub_drinks"]}[Data]
in
    pub_drinks

let
	Source = Cds.Entities("https://instance_2", null),
    entities = Source{[Group="entities"]}[Data],
    pub_drinks = entities{[EntitySetName="pub_drinks"]}[Data]
in
    pub_drinks

My question is, is it possible to create a single query to append the data from pub_drinks table in https://instance_1_URL , https://instance_2_URL , ... , https://instance_500_URL or do I have to create 500 queries?

TIA for your help!

LE 1: forgot to mention that I have a separate table in a system instance where I keep the URLs of all 500 database instances I must get the data from. Wondering if there would be a way to iterate over this system table in Power Query and run the query for each database.

[johnt75 · Accepted solution]

Turn the queries you have for the different tables into functions which accept the URL as the parameter.

Take copies, or references, to the table which holds the URLS, one copy or reference per table type. On each of these copies create a new column by calling a custom function and pass in to the function the URL column.

Expand the resulting new column and delete any columns you don't need.

[9427592754]

Thank you! Let me try this and get back.

LE: it works, thanks again.