Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Anonymous
Not applicable

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

11 REPLIES 11
Anonymous
Not applicable

I had the same issue in recent weeks. 

However, there is a setting saved my queries. 

Go to edit dataflow, under the Home tab, there is an button called "Option". Open the "Project options". 

FredBai_0-1658298312195.png

 

Tick on the second option, "Allow combining data from multiple sources, This could expose sensitive or confindential data to an unauthorized person". Then, try to refresh your dataflow. At least, my one works now. 

FredBai_1-1658298395517.png

 

 

Anonymous
Not applicable

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 🙂 

Anonymous
Not applicable

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

 

Very elegant workaround, thanks!

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

 

Anyways, thanks again

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors