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
tempranello
Advocate I
Advocate I

DirectQuery append

Hello there

 

I understand that Power BI Desktop currently provides limited functionality when using DirectQuery, but I'm having trouble establishing if I've bumped into the limit of its capabilities, or my own 🙂

 

I've a SQL database that contains a significant amount of data that I don't really want to import, so Power BI's DirectQuery concept is brilliant for me.

 

Previously, I used Excel PowerPivot to use pure SQL to import the data from three seperate tables (in the same DB) that each related to different types of tickets, eg. Incidents, Problems and Changes.  I simply ran three SELECTs with UNION calls to plop it all together. 

 

In Power BI Desktop however, I'm a bit stumped. 

 

I can easily select the three different tables using DirectQuery, but if I grab one of them and append the other two, I'm told that 'This step results in a query that is not support in DirectQuery mode'.  Is that so?

 

I have also tried querying just one of the tables, but then inserting the same SQL query that I used in my Excel PowerPivot.  The preview comes up perfectly, but I get an error against the Navigation step stating: 'Expression.Error:  The key didn't match any rows in the table.  Details:  Key=Record  Table =Table'.  I suppose that this is because I generated the query against a single table, but then hit it with a SQL statement that performs a UNION against another two.  Sheesh

 

Perhaps I'm just going about this all wrong.  The bottom line is that I don't want to import my data as there's lots of it, so DirectQuery is a godsend.  I need to bring the data from three seperate tables together so that I may then produce visualisations against all three, eg. Over a period of time, plot the total volume of incidents, changes and problems created.  If I could do it with the three tables being seperate then that'd be good too, as Power BI doesn't seem to have a problem with that, but I cannot find a way to plot the three onto a single line chart.

 

Alot to take in here.  I hope someone out there can give me some pointers?

 

Thanks for your help!

Aaron

1 ACCEPTED SOLUTION
HarrisMalik
Continued Contributor
Continued Contributor

@tempranello I dont understand why you are getting the error when you use your query with unions. I have tested the following query

 

SELECT A, B, C
FROM dbo.TABLE1

UNION 

SELECT A, B, C
FROM dbo.TABLE2

UNION

SELECT A, B, C
FROM dbo.TABLE3

It is working fine.

 

Steps:

1. Get data -> SQL Server

2. Give SQL Server name in first field

3. Give Database name in second field 

4. Open SQL Statement and paste the union query

5. Click load and select DirectQuery option

 

 

View solution in original post

6 REPLIES 6
HarrisMalik
Continued Contributor
Continued Contributor

@tempranello I dont understand why you are getting the error when you use your query with unions. I have tested the following query

 

SELECT A, B, C
FROM dbo.TABLE1

UNION 

SELECT A, B, C
FROM dbo.TABLE2

UNION

SELECT A, B, C
FROM dbo.TABLE3

It is working fine.

 

Steps:

1. Get data -> SQL Server

2. Give SQL Server name in first field

3. Give Database name in second field 

4. Open SQL Statement and paste the union query

5. Click load and select DirectQuery option

 

 

Thanks for that.  Very helpful!

 

@tempranello I was able to append in DirectQuery mode just fine. The resultant query should contain the union operation. 

 

Can you post the full query where it failed to fold (the one that shows Not supported in Direct Query Mode error)? Use View->Advance Query Editor to get the full query text.

OK, even though I can append the two tables and successfully fold this to the union query, something else is preventing me from actually loading this into the model. This uncovered an issue that we had in the DirectQuery pipeline which we will fix (hopefully soon). For now, your most viable option is to use the custom SQL query. Make sure you don't add any more steps after the source step with the SQL statement.

Is this fixed as i need to do more merges and therefore i cant use the sql statement...

pqian
Employee
Employee

@tempranello if you use custom SQL query, then I don't think you need the navigation step. Simply delete it.

 

Let me test a few things and get back to you for the append

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.