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
simonxiao
Regular Visitor

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 window, I selected 7 columns from both queries, and then the data table shows expected merged data.

 

But when I click "close and apply", it says "Mixing DirectQuery and imported data is not supported."

I confirmed that all of my original queries are "DirectQuery " - so what does the error message complaint about?

1 ACCEPTED SOLUTION

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

 

View solution in original post

6 REPLIES 6
ankitpatira
Community Champion
Community Champion

@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.

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.

:(:(:(

 

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

 

theerror.png

 

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

 

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.

simonxiao
Regular Visitor

I have two queries (DirectQuery) from Azure SQL Server.

Then I created two reference queries from that two original queries.

Now, I want to merge the two reference queries: in the merge window, I selected 7 matched columns from both first and second queries - and now the "Merged 1" query shows expected data table - so far so good.

But when I click "Close and Apply", then it says "Mixing DirectQuery and imported data is not supported.".

 

Where is the problem? I checked all my original queries are "DirectQuery".

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.