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

View solution in original post

2 REPLIES 2
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.
Highlighted
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!!

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 54 members 1,238 guests
Please welcome our newest community members: