Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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.
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".
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.
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 🙂
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.
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.... 🙂
Glad it worked. I hope other people find this post and fix their refreshes.
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.
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
Happy to help!
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,
Happy to help!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.