Power BI query to loop through a bunch of data sources?

[gckcmc]

OK Data nuts....I'm a newbie to Power BI, and am learning as I go....my programming background made me think initially of looping here, but have read in the forums that I'll be shot if I ask for this. 🙂

Here's my situation: I'm connecting to an Analysis Services data source.  In this source they have a parameterized cube I wish to use, and one of the mandatory fields is a project code.  I can't give a list or multiple values, and I have at least 25 of them to go through.  I need an appended query with all of them in one table.

I originally setup individual queries and called the interface each time, and then did an append query to glue them all together.  while tedious and error prone if I got the codes wrong, it is working.

I am assuming the codes will change over time, as will other parameters in this interface...so I setup a local table that would have the values needed, and I wanted to use them (thus the looping idea) to be used as variables in the Cube.Transform calls.

Any helpful ideas would be appreciated where I could take a column of project codes and iterate through them to make cube.transform calls and append (either as I go or the end)?

thanks!

[v-yulgu-msft]

Hi @gckcmc ,

Here is a similar thread for your reference: Loop API request and append results to one table.

@MarcelBeug and @ImkeF who are experienced in Power Query, have previously answered similar questions around looping, hope they could provide some suggestions here.

Regards,

Yuliana Gu

[ImkeF · Accepted solution]

Start from your local table that holds all the variables/parameter and add a column to it where you make your API call, thereby referencing the column(s) with your variables where needed. That will return a colum with all the values from the different rows of your table that you can simply expand. So no need to loop for this task.

If your code changes, you just have to edit it once, in this Table.AddColumn-step.

[gckcmc]

Hi ImkeF,

Thanks for the idea...I'm not sure if this can work so I post some code here to see if it's possible:

{..setup source and table from source...}

(I would normally call the Cube.Transform here with the 3 parameters listed as text values individually)

= Table.AddColumn(Custom1, "LE Codes", each Cube.Transform(Custom1,
{
{Cube.ApplyParameter, "ip_FiscalPeriodStart", {#"FinSP"}},
{Cube.ApplyParameter, "ip_FiscalPeriodEnd", {#"FinEP"}},
{Cube.ApplyParameter, "ip_CompanyCode", each{#"Country Codes"}},
....(chopped for brevity the remainder of the transform function)

}))

Now i have setup values for the first 2, which are just single values...they work fine in the cube.transform call.  The 3rd is a list of codes, and I want to run this transform call on each of the countries with all other pieces of the transform call being the same. 

would your idea still work, or would a looping structure be required?

thanks!

[gckcmc]

ok so I didn't get the loop figured out, but the solution for making a table with all parameters, and then building a custom function which calls the table's parameters lists, worked.  Marking that as a solution, thanks!

[wickert11]

Can you share how this was accomplished?  I have about 100 systems and I really don't want to set up each connection and query independently.