How can I loop through a list as soruce in Power Query?

[mariussve1]

Hello.

I am creating a model based on DMV's in Power Query, and now I want to loop through a list of all databases in one workspace, so I dont need one model for each database, but instad I want to have all the databases in one model.

So, this is my M code:
let
Tables = AnalysisServices.Database(Server, Database, [Query="select * from $SYSTEM.TMSCHEMA_TABLES", Implementation="2.0"])
in
Tables

The Server is a Parameter containing the XMLA endpoint URL to the workspace:
powerbi://api.powerbi.com/v1.0/myorg/WorkspaceName

The Database is just the name of the model:
ModelName

Now I want to have a list of all the models in one workspace, and loop throgh this in my m code:

let
Tables = AnalysisServices.Database(Server, "Here I want to loop through a list of Databases (power bi models), [Query="select * from $SYSTEM.TMSCHEMA_TABLES", Implementation="2.0"])
in
Tables

I actually want to get the list with rest API, so the list will therefore also be a table in Power query (I'm using the custom connector for API to get this list of datasets in a workspace).

Can someone please help me here? Thanks 🙂

Br

Marius

[mariussve1]

Not sure if this is correct, but my query to extract the list of models with API is now converted to a list. I can now use this list as a query into the Database parameter. I now want to invoke this list so it can loop through, but still stucked.

Marius

[ppm1]

You can loop through your list with List.Generate or List.Accumulate but it would be easier to just convert your initial list to a table and then add a custom column where you concatenate the dataset id into an API call on each row.

Pat