2 distinct data sources in one query

[jj1]

question

power query used to get data from 2 distinct sources

1 is a query that requires and is on prem gateway(MYSQL)

2 other is a query from non prem gateway- dataverse crm

no issue with query  no issue with initial publish to power bi service 

when refreshing it is indicating yes the deteverse source must also be placed on the on prem gateway since independently it does not need it but if included in query where other data source is on prem data source then yes it too must be on prem for refresh as it fetches data

Sound correct? i need to manually add that data source to the on prem gateway also in this instance?

[jj1 · Accepted solution]

i have solved this-thank you no action needed

[jj1]

ok so step 1 power bi desktop step 2 get on prem data source 1- step transform and do steps in power query like normal to edit data etc step 4 save in power query and apply to desktop power step 5 do same for non prom data source in its onw power query separate- step 6 then open one of the 2 power bi desktop and from there join the table in the data model step 7 then publish to power bi service step 8 service should then allow refresh without requiring both to be on prem gateway for refresh ? ok let me try this thanks

[lbendlin]

This is all done in the same Power BI Desktop PBIX. 

[lbendlin]

Do not merge on-prem and cloud data sources in Power Query. As you have experienced, this will result in the cloud data source requiring a gateway, forever, even after you have removed the on-prem data source.

Don't do it.  Leave these data sources separate in Power Query and join them in the Power BI data model.

If you insist on doing it anyway, convert your on-prem data source to a cloud data source (via dataflow or semantic model) before the merge.

[jj1]

what a good solution thanks for this insight big help

ok so instead first use power query to get on prem required mysql data source  and once i keep only the columns i want and i add my dax codes then save it . to power bi service right?

2then in paralell use separate power query with only dataverse data source (no gateway needed) and again establish data and publish to power bi service

3 then you are saying use data model instead? meaning within power query click the data model to do relationships between two query right? but wont i first need to have obtained the data source that is on prem into the query anyway? not clear how to do the data model you reference or what the steps are-can you send general steps please?

[lbendlin]

Create two queries in Power Query, one for your on-Prem MySQL and one for your Dataverse.

Load both queries into Power BI

Join the tables in the data model

[jj1]

sorry not clear here so query 1 is done- i took data soruce dataverse-did my power query transform and saved it in my power bi desktop

query 2 which has an on prem gateway is also done-again brought it into this same power bi desktop but not clear what you mean on how join table will somehow not require the one published report and dataset to not require both to be on prem for the automated daily refresh when publihsed to   power bi service so can you send me a smaple scren shot pls of a generic query 1 and query 2 on what you are recmmmedning is the steps?

[lbendlin]

Power Query:

Power BI:

[jj1]

so can you actually list the step you are doing to achieve your screen shot in power query and then power bi(not the realtionsuop) but the actual step you are doingin each one when you transform and save mysql in power query and then when you do same for dataverse(you are saying separate query right) not to add both in on query and then join in power bi table-(i did not do merge query so trying to see your exact step to achieve your solution where you sent screenshots-thank you

[lbendlin]

Please check the "data source settings" dialogue in either Power Query or Power BI. It will make the concept clearer.

[jj1]

sorry still an issue here-again ive used power query and service many times just this is first time to do with one on prem and one non on prem and yes goal is when publish not require both to be on prem.

So not clear still- step 1 is what exactly- connect to mysql data source and then in power query transform and save the file (mysql file in BI desktop)-right?

Step 2 then is from a new power bi desktop obtain the detaverse data source -transform in power query and save into desktop as dataverse.

you are first saying do 2 separate power query for each source but then you are saying connect id data model- so how are you adding the two separate queries into the same power bi without adding data source to the dataverse or the mysql BI desktop?