Data Appending from different servers

[Anonymous]

I'm importing data from mysql database , from DEV,ITG,PROD environment and creating a single dashboard which will show data of  all the 3 environment. All the tables and columnns in each environment are same but the data is different.

How can I differentiate the data is from a particular environment and how to append the data of all 3 environments.

Example:

DEV environment

DB_ID	DB_NAME	OWNER
1	xyz	Aman
2	ghj	aman

ITG environment

DB_ID	DB_NAME	OWNER
1	xyz	Aman
3	asd	aman

Combined data

DB_ID	DB_NAME	OWNER	Environment Type
1	xyz	Aman	DEV
2	ghj	aman	DEV
1	xyz	Aman	ITG
3	asd	aman	ITG

How to achive the combined data table in power bi(appending two environment data and a column of type)

[Anonymous]

Hi @Anonymous ,

I'd like to suggest you setting advanced t-SQL to add a custom column to store the source table name before you merge these tables, then you can simply combine these table records.

let
    Source = MySQL.Database("server1","table1",[Query="xxxxxx"]),
    Source2 =MySQL.Database("server2","table1",[Query="xxxxxx"]),
    Result= Table.Combine({Source,Source2})
in
    Result

Regards,

Xiaoxin Sheng

[Anonymous]

Is there any way other than combine/append as there are many tables and each time if I combine/append the tables the performance is poor and replication of data is more.

[Anonymous]

HI @Anonymous ,

You can try to do merge operation on the local file(e.g. put load data and merge data steps into excel worksheet), then you can use power bi to get the merged file, it should have better performance to loading data.  (merge operations on the huge amount of file will spend lots of resources and cause the performance issue)

I haven't found methods/options to effectively increase the performance of the query table who contains large amount of records and heavy calculation.
Regards,

Xiaoxin Sheng