Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Nestedjoin with SQL server and Exchange kills both connections

Hi!

 

I have run into a very irriating issue that I hope someone might be able to help me with. I have two data sources, exchange online where I download some mail and one sql server table. Both works perfectly, I connect directly to Exchange through the cloud and the sql server through the Gateway. 

 

However when I try to merge the querys in Power Query I run into trouble. I do a check in the SQL-table if the current ID from the mail exist or not. Everything works perfectly in the local file. 

 

When I upload the file to the Power BI Service I cannot connect anymore to either the SQL server or Exchange. When I look under data source credentials it seems that Power BI Service think I need a gateway for the exchange connection aswell as I have nested the querys. And as you know there is no gateway for Exchange, and ofcourse I shouldnt be needing it either as Exchange is in the cloud already. I would probably describe this as a bug in the service. 

 

Now I'm stuck, does anyone have a workaround for this? I'm thinking of a workaround to download the exchange data to another sql-table and join them instead, but would like to avoid it if possible. 

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    For reference after some more playing around with it. It seems I can't even append the two querys together when getting one from SQL server and the other from Exchange. Very disappointing.

     

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support

    Hi Anonymous 

    It requires a on-premise gateway or on-premise(personal) gateway to merge or append on-premises and cloud data sources.

    You need add the on-premise data sources as a data source under the on-premises gateway and give credential for web data source under the dataset setting in Power BI Service.

     

    Do you download a on-premises gateway and configure as below?

    install a gateway for Power BI

    Manage a gateway and configure schedule refresh

    merge or append on-premises and cloud data sources

     

    Best Regards

    Maggie