CTE Query Gives Error

[f_e_user]

Hello,

I've a model data made with Import Connectivity.
I've been tasked to convert it to DirectQuery and in several table they are all done with CTE.
I have a CTE SQL Query

 That 

• Runs fine on SQL Server when I launch this query
• I can see the data in PowerQuery

When I click "Close and Apply" in PowerQuery I do receive this error

1. Can you help me to solve this issue?
2. Does Direct Query Supports CTE like WITH ?

[AnushaSri]

Hi f_e_user,

You need to start With CTE query with a  Semicolon ";"

So it should be like ; With CTE 

Just add a semicolon before your CTE expression will solve

[grantsamborn]

Hi @f_e_user 

You might find this interesting.

https://stackoverflow.com/questions/58532581/re-writing-common-table-expression-cte-to-subquery-to-m...

[smpa01]

Hope this helps

https://stackoverflow.com/a/64217559/11156131

You can call each of the query tables seprately, create dat amodel, create measures and have it report exactly like the SQL above

[goncalogeraldes]

Hi @f_e_user, I think that you are only missing a ";". Try the following:

WITH NewestOS AS (
SELECT GS_OS_Data.ResourceID
	, TimeStamp
	, Caption0
FROM v_GS_Operating_System as GS_OS_Data
INNER JOIN (SELECT ResourceID
	, MAX(TimeStamp) as MaxDate
	FROM v_GS_Operating_System
	GROUP BY ResourceID
) MaxFilter on  GS_OS_Data.ResourceID = MaxFilter.ResourceID and MaxFilter.MaxDate = GS_OS_Data.TimeStamp
);
SELECT v_R_System.ResourceID 
  , v_R_System.Name0    
    , v_R_System.Name0 + '.' + v_R_System.Full_Domain_Name0 as Resource_Names0
    , v_R_System.Resource_Domain_OR_Workgr0 AS 'DOMAIN'
    , CASE v_R_System.Client0
            WHEN 1 THEN 'INSTALLED'
            ELSE 'MISSING'
        END AS ClientStatus
    , v_R_System.Client_Version0
    , v_R_System.Last_Logon_Timestamp0
    , CASE NewestOS.Caption0
        WHEN NULL THEN v_R_System.Operating_System_Name_and0
        ELSE NewestOS.Caption0
    END AS OSNAME
    , V_GS_System.SystemRole0
FROM v_R_System
LEFT OUTER JOIN NewestOS on v_R_System.ResourceID = NewestOS.ResourceID
LEFT OUTER JOIN v_GS_System ON v_R_System.ResourceID = v_GS_System.ResourceID
WHERE v_R_System.Operating_System_Name_and0 LIKE '%Microsoft%'

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[f_e_user]

It didn't solve the issue.