cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mattr_TEM Regular Visitor
Regular Visitor

Direct Query Transformation error

Hello,

 

I have an Azure DB and use Direct Query mode. I am trying to copy and past the following query into PBI:

 

SELECT * 
FROM (
  select 
      Concat('https://app.goformz.com/editor#?id=',[FormID]) As 'Form Link'
      ,[Top_Date]
	  --,Max(NextBillDate) As 'MaxNextBillDate'
	  --,[NextBillDate]
      ,[JobNumber]
      ,[BillingRequestNumber]
	  ,[Database_InvoiceToName]
	  ,[DatabaseText_InvoiceToStreet]
      ,[DatabaseText_InvoiceToCity]
      ,[DatabaseText_InvoiceToState]
      ,[DatabaseText_InvoiceToZip]
	  ,[Database_ShipToName]
      ,[DatabaseText_ShipStreet]
      ,[DatabaseText_ShipToCity]
      ,[DatabaseText_ShipToState]
      ,[DatabaseText_ShipToZip]
      ,[BillingRequestPONum]
      ,[FinalBill]
      ,[NextBillDate]
      ,[SalesL]
      ,[BillingRequestPeriod1]
      ,[BillingRequestPeriod2]
      ,[UnitTypeQty1]
      ,[UnitTypeQty2]
      ,[UnitTypeQty3]
      ,[UnitTypeQty4]
      ,[UnitTypeQty5]
      ,[UnitTypeQty6]
      ,[UnitTypeQty7]
      ,[UnitTypeQty8]
      ,[UnitTypeQty9]
      ,[UnitTypeQty10]
      ,[UnitTypeBeingBilled1]
      ,[UnitTypeBeingBilled2]
      ,[UnitTypeBeingBilled3]
      ,[UnitTypeBeingBilled4]
      ,[UnitTypeBeingBilled5]
      ,[UnitTypeBeingBilled6]
      ,[UnitTypeBeingBilled7]
      ,[UnitTypeBeingBilled8]
      ,[UnitTypeBeingBilled9]
      ,[UnitTypeBeingBilled10]
      ,[RequestAmount1]
      ,[RequestAmount2]
      ,[RequestAmount3]
      ,[RequestAmount4]
      ,[RequestAmount5]
      ,[RequestAmount6]
      ,[RequestAmount7]
      ,[RequestAmount8]
      ,[RequestAmount9]
      ,[RequestAmount10]
      ,[CreditRequestText]
      ,[CreditUnitTypeQty1]
      ,[CreditUnitTypeQty2]
      ,[CreditUnitTypeQty3]
      ,[CreditUnitTypeQty4]
      ,[CreditUnitTypeQty5]
      ,[UnitTypeBeingCredited1]
      ,[UnitTypeBeingCredited2]
      ,[UnitTypeBeingCredited3]
      ,[UnitTypeBeingCredited4]
      ,[UnitTypeBeingCredited5]
      ,[CreditRequestAmount1]
      ,[CreditRequestAmount2]
      ,[CreditRequestAmount3]
      ,[CreditRequestAmount4]
      ,[CreditRequestAmount5]
      ,[AddComments]
      ,[CreateTS]
      ,[EditTS]
  from [dbo].[JobBilling]
) AS TempBR
left outer join (
  select jobnumber, Max(NextBillDate) As MNBD
  from [dbo].[JobBilling]
  Group by Jobnumber
) as TempMaxNBD on TempBR.jobnumber = TempMaxNBD.jobnumber
where [BillingRequestNumber] != ' '
and FinalBill != 'Yes'

I am getting the "This query contains transformations that can't be used for a live connection" error.  This results fine in SSMS fine.  I believe it has to do with the temp tables, but I am not sure how to change it to get it to load in PBI. Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Mattr_TEM Regular Visitor
Regular Visitor

Re: Direct Query Transformation error

Thank you for the reply.  I went back through the query editor and noticed that the default query name, Query1, had a column in it that I had tried to change the type of and that transformation was being flagged in the error. I deleted the change column type step in the editor and the query loaded into PowerBi.  Once again thank you!!

2 REPLIES 2
Highlighted
v-yulgu-msft Super Contributor
Super Contributor

Re: Direct Query Transformation error

Hi @Mattr_TEM,

 

The main transformations in above SQL query is "Left Outer Join". I tested the similar query with live connection to local SQL DB, everything worked fine. In your scenario, to determine whether it is a problem caused by unsupported "Left Outer Join", please test with a simplified SQL code, just include Left Outer Join without any other transformations and filters.

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Mattr_TEM Regular Visitor
Regular Visitor

Re: Direct Query Transformation error

Thank you for the reply.  I went back through the query editor and noticed that the default query name, Query1, had a column in it that I had tried to change the type of and that transformation was being flagged in the error. I deleted the change column type step in the editor and the query loaded into PowerBi.  Once again thank you!!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 128 members 1,483 guests
Please welcome our newest community members: