Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!!
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
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!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |