BUG: Merge dataflow table with a non dataflow table results in refresh error on PBI Service

[mark_carlisle]

Overview:

Merge dataflow table with a non dataflow table results in refresh error on PBI Service. PBI Desktop refresh is fine.

Steps to Reproduce:

1. Create a report in Power BI with two tables. The first table should be from a source that is not a Power BI dataflow, e.g. a SQL server source. The second table should be sourced from a Power BI dataflow.
2. Merge the two tables.
3. Publish to Power BI Service.
4. Attempt an on demand or scheduled refresh.

Observed Behaviour:

Refresh fails. Below is an example of the error.

Something went wrong
Unable to connect to the data source undefined.
Please try again later or contact support. If you contact support, please provide these details.
Underlying error code: -2147467259 Table: NonVoice.
Underlying error message: The columns parameter must be null, specify the number of columns, specify a list of column names, or specify a table type.
DM_ErrorDetailNameCode_UnderlyingHResult: -2147467259
Microsoft.Data.Mashup.ValueError.Reason: Expression.Error
Cluster URI: WABI-US-EAST2-redirect.analysis.windows.net
Activity ID: 83703c77-276c-43e1-83ef-9790ea382b48
Request ID: ae46f037-b1dd-9558-316e-b59540007abf
Time: 2019-04-08 09:40:59Z

Expected Behaviour:

Refresh to succeed.

Workaround:

None.

Other Information:

I have attempted the following in trying to resolve;

• Used the Merge Queries as New option rather than Merge Queries.
• Have the dataflow table load into memory by checking Enable Load option within Power Query Editor.
• Rebuilt the Dataflows.
• Rebuilt the report in the latest version of PBI Desktop (2.67.5404.981 64-bit (March 2019)).
• Confirmed the gateway is on the latest version (3000.2.52 (March 2019 release 2)).
• Re-entered credentials for the dataflow in PBI Desktop and on the Service.
• Removed the merge (obviously this works but is an unacceptable solution as I need that data merged).

[evertaugustyns]

We are facing the same issue. Even the combination of Sharepoint Sources and Dataflows do not work.
We logged a ticket at Microsoft but did not receive any feedback so far.

[mark_carlisle]

I was able to replicate the issue with on-prem Excel files so it would seem the bug is to do with the gateway.

However I have now tested on the enterprise gateway with the April update and the issue appears to be resolved.

[Anonymous]

Hmm.. still seem to be having the same issue, not allowing for scheduled refreshes due to the dataflow not being able to go through the enterprise gateway.

[mark_carlisle]

@Anonymous Do you have this option enabled as my gateway shows as Running on [Server] as opposed to Not configured correctly.

The MS partner I was dealing with I don't think had a clue as to what was causing it, basically went quiet for two weeks. I think theres probably multiple causes and its just luck the April update as resolved my instance of it.

[Anonymous]

@mark_carlisle ,

That worked perfectly for this particular case; however, I also have one that tries to connect a network drive and a dataflow and even using this it doesn't work. But I was able to take the file from the network drive and place it into sharepoint and get it to refresh perfectly. Thank you for that information, that's a lot of help!

TestingTech