cancel
Showing results for 
Search instead for 
Did you mean: 

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

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).
Status: New
Comments
Frequent Visitor

Hello @mark_carlisle 

 

I have a very similar issue, I have created a dataflow, brought it into Power Bi desktop, and then added a few other queries that aren't dataflows, and it will not let me schedule a refresh through the gateway. I haven't found a workaround yet either.

 

Dataflow Error.png

Super Contributor

Hi @mark_carlisle ,

 

This issue should have been resolved. CRI: 105974070. Please test again to see if the fix applies to your scenario.

 

Regards,

Yuliana Gu

@v-yulgu-msft 

 

No this issue is still not fixed for me. I've published a verison of my report stripped back to two tables;

 

Untitled.png

 

The highlighted table is sourced from a dataflow. The dependancies are;

 

Untitled2.png

 

I've redacted the SQL server address. The Power Query;

 

let
    Source = Sql.Database("n0<REDACTED>", "<REDACTED>"),
    <REDACTED> = Source{[Schema="dbo",Item="<REDACTED>"]}[Data],
    #"Filtered TheDate from RangeStart" = Table.SelectRows(<REDACTED>, each [TheDate] >= #datetime(2018, 10, 1, 0, 0, 0)),
    // Removed TSL Skills and SagePay Retentions as these are mainly internal calls.
    #"Filtered SkillID and TotalCallsAnswered" = Table.SelectRows(#"Filtered TheDate from RangeStart", each [SkillID] <> 2833 and [SkillID] <> 2648 and [SkillID] <> 42 and [SkillID] <> 47 and [SkillID] <> 2422 and [SkillID] <> 2501 and [SkillID] <> 43 and [SkillID] <> 48 and [SkillID] <> 44 and [SkillID] <> 2420 and [SkillID] <> 2421 and [SkillID] <> 2909 and [SkillID] <> 2419 and [SkillID] <> 2800 and [SkillID] <> 2423 and [SkillID] <> 651 and [SkillID] <> 2771 and [SkillID] <> 2705 and not Text.Contains([SkillName], "Sales") and not Text.Contains([SkillName], "Loyalty")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered SkillID and TotalCallsAnswered",{"TheDate", "EmployeeNumber", "EmployeeName", "SkillID", "SkillName", "TotalCallsAnswered"}),
    #"Added InteractionSource" = Table.AddColumn(#"Removed Other Columns", "InteractionSource", each "Call"),
    #"Merged TelephonySkillsGroupings" = Table.NestedJoin(#"Added InteractionSource",{"SkillID"},TelephonySkillGroups,{"SkillId"},"TelephonySkillsTable",JoinKind.LeftOuter),
    #"Expanded TelephonySkillsGroupings" = Table.ExpandTableColumn(#"Merged TelephonySkillsGroupings", "TelephonySkillsTable", {"Product"}, {"Product"}),
    #"Added LineupKey" = Table.AddColumn(#"Expanded TelephonySkillsGroupings", "LineupKey", each Text.Combine({[EmployeeNumber], Text.From(Number.From(Date.StartOfMonth([TheDate])))},"-"), type text)
in
    #"Added LineupKey"

When published to the service and a refresh attempted I get the same 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: Voice.
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: 0371849d-acc2-40eb-872d-59ab966e43d2
Request ID: d7c4a999-050b-bfc2-b20d-bdd079b29a0f
Time: 2019-04-12 07:46:08Z
Visitor

I can confirm what Mark is saying, this still hasn't been fixed

Member

this issue is still there

Regular Visitor

I seem to have same issue/error.  One of my queries is an Append between Dataflow table and non-Dataflow tables, and failure to refresh on PBI Service. 

Frequent Visitor

I'm still having the issue as well, and there doesn't seem to be any work around at the moment except to just manually refresh and publish the report when you need it until this is fixed.

@TestingTech  I've had to remove the dataflows that merge in my reports as a workaround just so I can schedule, obviously not ideal but saves time over refreshing manually. I'm on a support call with Microsoft tomorrow. I doubt there'll be a solution but we'll see.

Visitor

Having the same problem

 

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 
Underlying error messageThe 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.ReasonExpression.Error
Cluster URIWABI-EUROPE-NORTH-B-redirect.analysis.windows.net
Activity ID6dcc92b5-cb21-4004-bf8d-318f8411b0c8
Request ID64d7d2d6-fdf2-cfdd-c3a1-a07c4d210b9a
Time2019-04-17 16:26:13Z
Frequent Visitor

With the April update of the Gateway I've been able to connect the dataflows with the other sources; however, that is only with the personal gateway, my enterprise gateways still do not allow the dataflows to be refreshed when mixed with other sources that are not dataflows. Anyone experiencing the same issue?