Looping over different data sources

[papanovn]

Hello.

I'm experienced QlikView developer but I'm still novice to Power BI. I need expert advise about the following. In QlikView I'm able to loop large amount of code using variables. Let's look at the following example. Imagine that your customer has 10 companies. Every company has the same database structure but there is a difference in the table name. It's something like dbo.[CompanyName$TableName]. In QlikView I'm looping over all companies with simple script. I'm defining one variable for the company name and then I'm executing the same code as many times as company count is passing the variable as compnay name. Here is an example:

LET varCompanyCount = FieldValueCount(Company Name);

// I'm selecting all the companies in one table to know the company count.

FOR i=1 to varCompanyCount

    LET varCompanyName = FieldValue(CompanyName, $(i));

    //Looping trough every company

   SELECT *

   FROM dbo.[$(varCompnayName)$Customer];

NEXT i

Thus QlikView will concatenate automaticaly every customer table from every company in one common Customer table. I know that there is no such thing like automatic concatenation of tables in PBI. However, this is is not the point. The point is how to execute same script many times with one simple parameter which is a part from the table name. I don't wanna make as many selections as many companies I have. It is a lot of work and every correction in the code will cost a large amount of time. Any ideas?

[Greg_Deckler · Accepted solution]

Sure you can automatically concatenate tables with an Append command in M. I believe what you are looking for is to have a function that contains your common import code. Then, you would just pass the correct parameters into the function and your function would return a table which you would feed to the Append command. You main query script would probably include a List of the names that you would iterate over versus a FOR loop.