DM_GWPipeline_Gateway_MashupDataAccessError joining SQL with Sharepoint Excel in PQ

[datadonuts]

Hi all,

we are running in a refresh failure in the Service, when using a join between a SQL RDB Source and an Excel file on Sharepoint / Sharepoint folder in Power Query. The sources have been sucessfully loaded, joined (left outer) and updated in PBI Desktop, but the dataset fails to refresh when uploaded into Service. We temporarily solved the problem by building a relationship in the datamodel btw. the Sharepoint Excel file (helper table "Kundengruppen") and the SQL customer table "Kunden", instead of merging them in PQ, then the refresh works.

Thus it seems to be an issue with the join in PQ causes the error, but we have no clue, how to resolve it, if we want to keep that step. We already changes the PQ scriped, run several tests, looked through all the comments here and online, but could not find an appropriate solution. 

Here are the PQ code as well the Error message.

We hope someone can help on this issue. 

Sharepoint source

Quelle = Excel.Workbook(Web.Contents("https://xxxxxxx.sharepoint.com/sites/SP-XXXX-ALL-Team_Business_Applications/Freigegebene%20Dokument...CustomerGrouping.xlsx"), null, true),
Kunden_Sheet = Quelle{[Item="Customes",Kind="Sheet"]}[Data],

SQL Source

let
Quelle = Sql.Database("XXXXXXX", "XXXXXXX", [Query="SELECT * FROM Customers WHERE CustomerCompany IN (1,2,3)", CommandTimeout=#duration(0, 0, 1, 0)]),

....
#"Zusammengeführte Abfragen" = Table.NestedJoin(#"Geänderter Typ", {"Firma_Kundennummer"}, CustomerGrouping, {"Firma_Kundennummer"}, "CustomerGrouping", JoinKind.LeftOuter),
#"Erweiterte Kundengruppen" = Table.ExpandTableColumn(.....),
#"Umbenannte Spalten" = Table.RenameColumns(.....)
in
#"Umbenannte Spalten"

Fehler bei der Datenquelle: {"error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","pbi.error":{"code":"DM_GWPipeline_Gateway_MashupDataAccessError","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"We're sorry, an error occurred during evaluation."}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}}],"exceptionCulprit":1}}} Table: Customers.
Cluster-URI: WABI-GERMANY-WEST-CENTRAL-PRIMARY-redirect.analysis.windows.net
Aktivitäts-ID: bc6d4e84-33df-4130-a8fc-2136fd36f5ad
Anforderungs-ID: c32cbdd0-af5b-4a5e-cfb3-6402eb1b3831
Zeit: 2023-02-27 14:56:55Z

Any advice is welcomed and highly appreciated.

[lbendlin · Accepted solution]

The sources have been sucessfully loaded, joined (left outer)

This. This right here. You should generally avoid merges, and you should especially avoid merges involving on-prem and cloud data sources. Because if you do you need to reconfigure your gateway to allow refresh of cloud data sources, and to add insult to injury you then also need to create a gateway connection for your cloud sharepoint (!!!) and you cannot get rid of that one once you went that route.

Don't do any merges in Power Query. Let the Vertipaq data model do the work for you.