Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data Appending from different servers

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_IDDB_NAMEOWNER
1xyzAman
2ghjaman

 

ITG environment

DB_IDDB_NAMEOWNER
1xyzAman
3asdaman

 

Combined data

DB_IDDB_NAMEOWNEREnvironment Type
1xyzAmanDEV
2ghjamanDEV
1xyzAmanITG
3asdamanITG

 

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

Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

        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