cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
meehael
Helper II
Helper II

Dataflow Refresh Error (Merge Queries)

Hi everyone,

 

a dataflow is failing to refresh all of a sudden, since last week.

I'm getting this error:

Error: Data Source Error : DataSource.Error: Microsoft SQL: Invalid object name 'schema$326d9071-b0c6-418b-9f83-54a9fa32c2e1.5976843a-b7d8-4f19-893c-065a7dee6f99:businessunit'. DataSourceKind = SQL DataSourcePath = mydb.database.windows.net

We didn't change anything in the schema.

 

The query which throws the error is this:

let
  Source = Sql.Database("mydb.database.windows.net", "mydb"),
  #"Navigation 1" = Source{[Schema = "dbo", Item = "systemuser"]}[Data],
  #"Choose columns" = Table.SelectColumns(#"Navigation 1", {"Id", "isdisabled", "businessunitid", "usersymbol", "title", "departmentuser", "domainname", "fullname"}),
  #"Merged queries" = Table.NestedJoin(#"Choose columns", {"businessunitid"}, businessunit, {"Id"}, "businessunit", JoinKind.LeftOuter),
  #"Expanded businessunit" = Table.ExpandTableColumn(#"Merged queries", "businessunit", {"name"}, {"businessunit.name"})
in
  #"Expanded businessunit"

What I've noticed is that if I remove the "Merged queries" Step with the nested join, it runs fine, so it seems something got broken in the service, when Nested Join is used?

 

I've tried to create a new dataflow with similar query, but it's also affected.

 

This started happening last week and is still in effect.

 

Other dataflows which don't use Merge Queries step (Nested Join) refresh just fine.

 

Thanks in advance and best regards,

Mike

1 ACCEPTED SOLUTION
banderson_can
Frequent Visitor

Anyone experiencing Dataflow refresh failures should try this fix.  Edit the DF settings and disable the "Enhanced compute engine settings."  Then apply and let it make the changes. Then try a refresh.

 

banderson_can_0-1634740812373.png

 

View solution in original post

10 REPLIES 10
Per-J
Helper II
Helper II

Same issue. Microsoft, this needs to be fixed fast. My dataflow suddenly stopped loading some days back, and navigating through the error log, it looks like the query thinks a MDI-table in the dataflow itself should be fetched from the SQL server. In the steps pane i see that "merge queries" is set to query folding. Is that a bit strange considering that the merged table is not from the SQL database?


It works when I turn off enchanted computing, but I need that for my downstream dataflows to function correctly, so still my solution is broken..

 

UPDATE: 
Found a solution for this problem that works in my scenario. 
I both loaded the tables and did transformations in the same dataflow. This is not best practice according to the Microsoft documentation on dataflows. Rather, one should split these flows into two different dataflows, one for stage and one for transformation (splitting the etl process). 
After altering the design by having dataflow #2 referencing the enteties in the stage dataflow, there are no errors occurring. Another benefit is that the load on the source seems to be significantly lighter as well, as all dataflows in my output layer (step #3) now only reference the second dataflow and not the source directly. 

Hope this can help someone else too 🙂 

banderson_can
Frequent Visitor

Anyone experiencing Dataflow refresh failures should try this fix.  Edit the DF settings and disable the "Enhanced compute engine settings."  Then apply and let it make the changes. Then try a refresh.

 

banderson_can_0-1634740812373.png

 

View solution in original post

Very elegant workaround, thanks!

However, this still seems like a general bug to me.

 

Anyways, thanks again

@banderson_can that worked like a charm for me! Safer to stay with the old fashion engine instead of trying to optimize.... 🙂

Glad it worked. I hope other people find this post and fix their refreshes.

pedrocrs
New Member

I'm having the exact same issue with a dataflow that has been built in March and remained unchanged since then.

 

No changes to the database either.

 

Seems that the merging tables step is searching for a table in the SQL Database when it should just take the reference of the custom table that has been created in the same dataflow.

ibarrau
Super User
Super User

Hi. The code is looking good. However the error says 

Invalid object name 'schema$326d9071-b0c6-418b-9f83-54a9fa32c2e1.5976843a-b7d8-4f19-893c-065a7dee6f99:businessunit'.

That sounds like you have that "schema$....." in some place wrongly written. Because the only step here failing is the merge, I would suggest checking the "businessunit" table code. That is the table merging and it might have the problem of the error. Can you show us that code?

I hope that make sense


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hi,

 

thanks for the reply.

The "businessunit" table code is this:

let
  Source = Sql.Database("mydb.database.windows.net", "mydb"),
  #"Navigation 1" = Source{[Schema = "dbo", Item = "businessunit"]}[Data],
  #"Removed other columns" = Table.SelectColumns(#"Navigation 1", {"Id", "name"})
in
  #"Removed other columns"

Thanks in advance!

That's weird. Do you have any other table that won't let you refresh the preview?

Even though I'm not finding the mistake in the M code, I can say that merging data is better to perform it in the database than in Power Query. If both of the sources you have there are from the same database, why don't you try joining them in a native query? that way you can get rid of the merge step and it will run even faster.

I hope that helps,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

What's funny is that data preview works fine when designing the dataflow. However, after saving the dataflow and trying to refresh it, the refresh fails.

 

The dataflow has about 20 different queries and it's also handy to have the definitions in the dataflow, so that I don't have to create SQL views in the database.

 

But I tried to replace the "systemuser" and "businessunit" ones with an sql view and it works as a workaround.

 

However, it's weird it stopped working all of a sudden.

 

Thanks

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors