Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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$...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

     

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    4 years ago

    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,

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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