Forum Discussion

mariussve1's avatar
mariussve1
Solution Sage
3 years ago

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

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

Need Help

2 Replies

  • mariussve1's avatar
    mariussve1
    Solution Sage
    3 years ago

    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's avatar
      ppm1
      Solution Sage
      3 years ago

      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