Forum Discussion
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.netWe 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
11 Replies
Anonymous that worked like a charm for me! Safer to stay with the old fashion engine instead of trying to optimize.... 🙂
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.
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
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,
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.
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 🙂
