Direct Query Transformation error

[Mattr_TEM]

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!

[v-yulgu-msft]

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

[Mattr_TEM · Accepted solution]

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!!