M query combining multuiple tables of the same sturcture

[phcampbell01]

Hi All,

So i am trying to create a scalable script to easily combine multiple tables (of exact same structure)

At present i have the following which works fine.

"let
    Table1 = "subdomain1",
    Table2 = "subdomain2",
    Table3 = "subdomain",
    Source1 = OData.Feed("https://api.xxxx.com.au/odata/v1/"& Table1 & "/Clients", null, [Implementation="2.0"]),
    Source2 = OData.Feed("https://api.xxxx.com.au/odata/v1/" & Table2 & "/Clients", null, [Implementation="2.0"]),
    Source3 = OData.Feed("https://api.xxxx.com.au/odata/v1/" & Table3 & "/Clients", null, [Implementation="2.0"]),
    CombinedTable = Table.Combine({Source1,Source2,Source3})
in
    CombinedTable"

ideally i would like to have a parameter list of subdomains such that this works for any number of subdomains.. just not sure about how to do this in M query language, or if its even possible...

looking forward to idea.s

PC

[Jimmy801 · Accepted solution]

Hello @phcampbell01 

you could create a table with one column where you put your domain-names in. Then you add a new column where you put a formula like this

 OData.Feed("https://api.xxxx.com.au/odata/v1/"& [SubdomainColumn] & "/Clients", null, [Implementation="2.0"])

After this step you should get a table in every cell. Then add a new step, where you combine this tables, referencing the new created table column. Your code should look like this

FinalStep = Table.Combine(YourPreviousStepName[YourNewColumnName])

So whenever you add a new value into your starting table, it will be dynamically added to your final combined table

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[phcampbell01]

Hi All,

So been working with this solution and getting the data with no problems. the issue i now face is that you cant publish and schedule refreshes due to the dynamic nature of the query.

is there a way to solve this or work around it?

appreciate your thoughts.

PC

[phcampbell01]

Brilliant!

Worked a treat. thanks so much.

PC