MySQL data import: importing multiple client dbs, query folding, advanced queries

[Laila92]

I am trying to figure out the best way to import my data. 

The data sits in MySQL, but has multipe databases for different ID's. I want only some of the tables from each db for each client. 

If possible, I want to apply incremental refresh on these tables.

Currently, I have used this answer (https://community.powerbi.com/t5/Desktop/Create-tables-based-on-all-Query-Parameters/td-p/345947) to important the tables of multiple clients from MySQL, so I took the following steps:

1. Made a table with all client IDs.

2. Made a function to pull data from the DB with a client ID parameter

3. Added a column to the table from step 1 with the custom function from nr 2.

My main questions: 

1. 

= (Query1) => 
    let
        Source2 = MySQL.Database("xxxxx.rds.amazonaws.com", "Query1",[ReturnSingleDatabase=true,clients=true])

    in
        Source2

See the custom function. How can I add to only grab some tables, for example the table named "clients" (thats what I tried with [ReturnSingleDatabase=true,clients=true] but that does not work)? 

2. Is query folding possible with this, i.e. incremental refresh?

[v-juanli-msft]

Hi @Laila92 

The link you followed needs the same data structure for every table.

If your tables/databases have different data structure, please create a parameter with connection information, then use the parameter in your new query.

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-parameters-in-power-b...

As for query folding, please learn from the reference below when it don't support query folding.

https://docs.microsoft.com/en-us/power-query/power-query-folding#transformations-that-prevent-foldin...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Laila92]

Thank you. I just somehow need to get the same tables for all the ids, so somehow loop over the list of ids.