Append Queries Question

[schwinnen]

I have to do identical reports from 2 differerent PostgresSQL databases.  Ideally, I would be able to merge the two queries so that I just have to create the report once.  However, I would still like to be able to identify which database the information came from so I can filter.  One database is TEST and one is PRODUCTION.  They both have identical fields and there is nothing in the data that would indicate which database each record comes from.  

Anyone have any ideas on this?  Is there a way to indicate which database each record comes from that would also apply to new records when I refresh?

[petrovnikitamai]

for example, if u get data from odbc

Try to create table with db_id like

and add new column with subquery like

=Table.AddColumn(Source, "Custom", each Odbc.Query([db_name],"select * from table_name"))

Next step expand data and create new primary key, merge db_name and primary key table. 

Unfortunately, this model don't support scheduled update in power bi service.

[PattemManohar]

@schwinnen Try adding an additional field in your SELECT statement (using SQL Statment) option while importing. Something, similar to this...

SELECT c1,c2,'TEST' as DB FROM TEST.Table1

UNION ALL

SELECT c1,c2,'PRODUCTION' as DB FROM PRODUCTION.Table2

[schwinnen]

Thank you.  I will try this when I have a chance.  It will be more complicated than what you have provided because the name of the databases and the tables contained within them are longer than what I stated earlier.  And as I am not an expert at SQL, I will have to figure out exactly how to write my statement. 

[v-chuncz-msft · Accepted solution]

@schwinnen,

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/displaying-source-table-name-in-append-field/td-p/485400

[schwinnen]

That worked.  Thank you very much!