Forum Discussion

simonxiao's avatar
simonxiao
Regular Visitor
9 years ago
Solved

error when merge query: Mixing DirectQuery and imported data is not supported.

I have two queries from Azure SQL Server, with "DirectQuery" mode. Now I created two reference queries based on that 2 queries; and then merge the two refernece refernece queries. In the Merge windo...
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi simonxiao,

     

    I just tested the merge queries from my side with Azure SQL Database, with Direct query mode. And the merge works.

    What I tried here is, get the two queries separately, click load for both of the two queries, after that, Under Power BI desktop Home tab, click Edit queries. Then do the customizations there.

    One aspect should mention is that the Enable Load(right click the table at the left side of Query Editor) is also the same as import, make sure the queries are all loaded(or queried at the same time). Then the merge should work.

     

    Regards

     

ankitpatira's avatar
ankitpatira
Community Champion
9 years ago

simonxiao I can only guess that it is treating referenced queries as Imported queries. Perhaps can you try merging two original queries without referencing them and close and apply, this will confirm if my guess is correct or not. If that is the case I would say rather than referencing original queries, right click and duplicate them and then merge.

simonxiao's avatar
simonxiao
Regular Visitor
9 years ago

No, I tried to merge the two original queriesqueries, and still the same problem.

The bad thing is, PowerBI just report ambigous message, but did not tell me which one is DirectQuery and which one is Imported data.

:(:(:(

 

  • simonxiao's avatar
    simonxiao
    Regular Visitor
    9 years ago

    More details: this is the merged query:

     

    let
    Source = Sql.Database("mytest.database.windows.net", "mytest"),
    dbo_Perf_Next = Source{[Schema="dbo",Item="Perf_Next"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Perf_Next, each Date.IsInPreviousNDays([TestDate], 56) or Date.IsInCurrentDay([TestDate]))
    in
    #"Filtered Rows"

     

     

    let
    Source = Sql.Database("mytest.database.windows.net", "mytest"),
    dbo_Perf_DefaultKernel = Source{[Schema="dbo",Item="Perf_DefaultKernel"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_Perf_DefaultKernel, each Date.IsInPreviousNDays([TestDate], 56) or Date.IsInCurrentDay([TestDate]))
    in
    #"Filtered Rows"

     

     

    let
    Source = Table.NestedJoin(Perf_Next,{"HostType", "OSType", "Size", "NumberOfConnections"},Perf_DefaultKernel,{"HostType", "OSType", "Size", "NumberOfConnections"},"NewColumn",JoinKind.LeftOuter)
    in
    Source

     

     

    • v-micsh-msft's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi simonxiao,

       

      I just tested the merge queries from my side with Azure SQL Database, with Direct query mode. And the merge works.

      What I tried here is, get the two queries separately, click load for both of the two queries, after that, Under Power BI desktop Home tab, click Edit queries. Then do the customizations there.

      One aspect should mention is that the Enable Load(right click the table at the left side of Query Editor) is also the same as import, make sure the queries are all loaded(or queried at the same time). Then the merge should work.

       

      Regards

       

      • simonxiao's avatar
        simonxiao
        Regular Visitor
        9 years ago

        Thanks. I disabled "Enable load, and then re-enable it; and then "Merge Queries" succeeded. But "Merge Queries as New" still report same problem.

        I beleive there is a bug in the PowerBI desktop.