Append rows in direct query

[Anonymous]

Hi All,

Hope you're doing good.

I need to monitor the SQL job status in Power BI. To do that I'm connecting msdb.dbo.sysjobs tables and retrieving records in power BI with direct query and I'm able to see the job status. I've been asked by my manager to show the Job status for multiple servers (e.g Dev,UAT and Prod enviroment) but the problem is I've to show all server job status in single visual. and to do that I tried appending records in one table but the moment I append the rows in one table it is silently converting my connection to Import mode.

This is how my visual should look like. 

CountryName	Environment	Job Name	Status
India	Dev	Load Stg Data	Running
India	UAT	Load Stg Data	Error
India	Prod	Load Stg Data	Success
France	Dev	Load Data Mart	Success
France	UAT	Load Data Mart	Success
France	Prod	Load Data Mart	Running

Please let me know any workaround to get the desired result. 

Note - I can not use Import mode. I've to connect is with direct query only.

[Anonymous]

Hi @jaideepnema I can not use union in sql server as my tables are on diffrent server. if at all I want to use union then I'll need to create linked server.

[jaideepnema]

Hi @Anonymous ,

did you tried using a query like this :

select countryname,environment,jobname,status from table a

union

select countryname,environment,jobname,status from table b

I would suggest if you can modify the sql query  or you can also a view which has all the data from multiple servers.

Solved: Edit Existing SQL - Microsoft Power BI Community

Please accept this as a solution if your question has been answered !!

Appreciate a Kudos 😀