Gateway data refresh fails when data is merged from web and on-prem sources

[Sokon]

Datasets that include a query that merges data coming from a web data source on one side and another set of data coming from an on-Premises data source can't be refreshed using the On-Premises data gateway.

Error messages depend on the kind of the web data source. For example:

• Sharepoint Online List: "The credentials provided for the SharePoint source are invalid"
• Excel in Sharepoint Online (Web Connector): "SharePoint: Request failed: https://[...]"

I tried to merge data

• from On-Prem Oracle with SPO List
• from On-Prem Excel with SPO List
• from On-Prem Excel with Excel in Sharepoint Online (Web Connector)

Every attempt failed. Tested with On-Prem Data Gateway 12-2018. I followed this documentation. According to this announcement this should work.

However, refreshes succeed when I access the identical data sources with reports that do NOT merge the data in a query:

[v-qiuyu-msft]

Hi @Sokon,

I tested on my side with your three scenarios, dataset refresh are successfully. In your scenario, I would suggest you go to Dataset Settings pane for the dataset, reenter the credential for online data source, then refresh it again.

Also please update your on-premises data gateway to the latest version as ours.

Best Regards,
Qiuyun Yu

[jeffshieldsdev]

Are you reformatting your Excel web URL? Like this: "You have to strip out the :x/r/ from the middle of the URL and the question mark and everything after from the end and then it'll work."

Also, check this post to see if has your solution: https://community.powerbi.com/t5/Service/FIXED-Error-quot-Failed-to-read-GatewayCore-property/m-p/59...

[Sokon]

Hi @v-qiuyu-msft, thanks for your answer,

Re-entering the credentials didn't help. (I know about that trap, and i had to enter credentials in all other test datasets as well in order to access the web sources.)

As to installing the latest gateway version: Your screenshot doesn't show the currently availlable version. The latest one that I can download here is 3000.0.144.3 from 12/20/2018. This is the one I tested with.

However, may I ask how you tested the "MERGE" of data? We might talk about different things!

When I talk about merging I mean calling the M function Table.NestedJoin in a simple query like this:

let
    Source = Table.NestedJoin(#"On-Prem Excel",{"Key"},#"Web DS",{"Key"},"Tabelle1",JoinKind.LeftOuter)
in
    Source

In this query, data from both sources are MERGED into one table.

• Before I added this query to my dataset, everything works and data can be refreshed by both Power BI Desktop and Gateway.
• After adding this simple query, only Power BI Desktop can refresh the data, but not the gateway.

Does this query work in your own tests?

@jeffshieldsdev:

Yes, there are no "/x/r" and no "?web=1" in the URL. The M code for getting the Excel in SPO is identical with the one in the datasets that can be refreshed. Here is the code for the query "On-Prem Excel" mentioned in the code snippet above.

let
    Quelle = Excel.Workbook(Web.Contents("https://mycompany.sharepoint.com/sites/XYZABC/Shared%20Documents/Merge%20Test%20SPO%20Excel.xlsx"), null, true),
    Tabelle1_Sheet = Quelle{[Item="Tabelle1",Kind="Sheet"]}[Data],
    #"Höher gestufte Header" = Table.PromoteHeaders(Tabelle1_Sheet, [PromoteAllScalars=true]),
    #"Geänderter Typ" = Table.TransformColumnTypes(#"Höher gestufte Header",{{"Key", Int64.Type}, {"Value", type text}})
in
    #"Geänderter Typ"

Besides, if there would be an error in the SPO Excel data extraction, the same error wouldn't occur in the SPO List source. Additionally, as I said, everything works find with the PBI Desktop...

[Anonymous]

Hi,

We also have exactly the same issue. I.e. gateway can refresh dataset successfully if it has separate queries using on-prem data source (SQL Server) and online data source (Excel on SharePoint online folder).

However once we merge the on-prem and online data sources together into one query, the gateway is unable to refresh the dataset.

The only work around we have so far is to use the On-premise data gateway in Personal Mode (i.e. run it on a PC). We have installed the latest version of personal gateway as at 08/01/2019 (3000.0.265.0)

Regards, Carlton

[v-qiuyu-msft]

Hi @Sokon, 

Yes, I tested with Merge Queries and Merge Queries as New features but not able to reproduce the issue on my side. Till now, it's hard to provide further support from forum side, I would suggest you create a support ticket to let the engineer look into the issue on your side. 

@Anonymous Please create a support ticket as well. 

Best Regards,
Qiuyun Yu 

[Sokon]

@v-qiuyu-msft: Thanks for the confirmation and your help. I will create a support ticket!

[Anonymous]

Hi All,
I am getting this same error message after connecting my datasources to the On-premise Gateway for other sources in the report.
The SharePoint datasource is giving me the error "SharePoint: Request failed: Unable to connect to the remote server" when I initialize a refresh from the Power BI service or when i schedule a refresh through the Gateway.
If i initialize a refresh through the Power BI Desktop, it works perfectly fine.
Also it does not complain about the file that I am loading but complains about the another table where I am merging the SharePoint file with another file
All the steps mentioned in this document has been performed and still the same error message.
Please if you can help.

@SokonHow is the support ticket you created going and do you have a number or link to it that I can reference?

Thanks

[Sokon]

@Anonymous: The support ticket numer is 119011619554863

However, I found this post about data privacy levels that might explain (but not solve) the behaviour. Reason is, in my test cases no gateway data source can be used because the Web data connector type necessary to access SPO can't be configured using OAuth2. If data sources really automatically get "private" data security level when executed in the service like explained in the blog post, of course joining might become an issue.

[Anonymous]

Hi all,

Here is the support ticket no. that I have raised for this issue: 119011419547011

Cheers,

Carlton.

[Anonymous]

@Sokon

In my case I have 4 datasources which includes the SHarePoint online source that is getting the error message. I am not using any privacy level for any of the data sources.

@Sokon @Anonymous Will you guys be willing to update your support ticket instead of me creating a new one?

I had chat with one of the support guys and was told to create a support ticket or update an old one for someone to take a look.