Direct query or translation from SQL code to power bi

[LucienF38]

Dear all,

I've been stuck on a big issue for a long time and the community is my last hope !! 

I have a SQL code that I want to reproduce into power BI. I've been trying all my ideas in power query editor and can't get rid of it...

I know that power BI can recognize an SQL code in direct query but I would prefer to compute it in load mode. Also, when I'm trying to launch my code through direct query it's asking me a confirmation to act on the native database. Considering my code, does anyone know what are the risks ? 

Also, does anyone can help me to "translate" the highlighted lines below ? I can't compute the "match between orders and quotation" lines.

Somes indications :

- CO = Orders

- Q = Quotations

Thanks for helping  

/* Transformation rate of quotations into orders */
/*Lines of Orders */
IF OBJECT_ID('tempdb..#tmp_SOL_Quot1') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot1 END
SELECT  OOLINE."ORNO - Customer order number"
                                , OOLINE."PONR - Order line number"
                                , OOLINE."ITNO - Item number"
                                , OOLINE."ORST - Highest status - customer order"
                                , OOHEAD."CUNO - Customer"
                                , OOLINE."RGDT - Entry date"
                                , CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key CO"
INTO      #tmp_SOL_Quot1
FROM   M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
                                INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
                                ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" > '05'
                                AND OOLINE."ORST - Highest status - customer order" < 99
                                --AND OOLINE."RGDT - Entry date" >= 20180101

/* Lines of quotations*/
IF OBJECT_ID('tempdb..#tmp_SOL_Quot2') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot2 END
SELECT  OOLINE."ORNO - Customer order number"
                                , OOLINE."PONR - Order line number"
                                , OOLINE."ITNO - Item number"
                                , OOLINE."ORST - Highest status - customer order"
                                , OOHEAD."CUNO - Customer"
                                , OOLINE."RGDT - Entry date"
                                , CONCAT(OOLINE."ORNO - Customer order number",OOLINE."PONR - Order line number") AS "Key Q"
INTO      #tmp_SOL_Quot2
FROM   M3JDTP600."V_OOLINE - TF: CO line file (OB)" OOLINE
                                INNER JOIN M3JDTP600."V_OOHEAD - TF: CO header file (OA)" OOHEAD
                                ON OOHEAD."ORNO - Customer order number" = OOLINE."ORNO - Customer order number"
WHERE OOLINE."ORST - Highest status - customer order" = '05'
                                --AND OOLINE."RGDT - Entry date" >= 20180101

/* Match between orders and quotations*/
IF OBJECT_ID('tempdb..#tmp_SOL_Quot') IS NOT NULL BEGIN DROP TABLE #tmp_SOL_Quot END
SELECT  *
INTO      #tmp_SOL_Quot
FROM   (SELECT TTQ."ORNO CO"
                                                                , TTQ."PONR CO"
                                                                , TTQ."Entry date CO"
                                                                , TTQ."ORNO Q"
                                                                , TTQ."PONR Q"
                                                                , TTQ."Entry date Q"
                                                                , RANK() OVER (PARTITION BY "Entry date Q","Key Q" ORDER BY "Key Q","Entry date Q",TTQ."Entry date CO","Key CO" ASC) AS Ranking
                                                                , [Key CO]
                                FROM   (SELECT *
                                                                FROM   (SELECT #tmp_SOL_Quot1."ORNO - Customer order number" AS "ORNO CO"
                                                                                                                                , #tmp_SOL_Quot1."PONR - Order line number"                AS "PONR CO"
                                                                                                                                , #tmp_SOL_Quot1."RGDT - Entry date" As "Entry date CO"
                                                                                                                                , #tmp_SOL_Quot2."ORNO - Customer order number" AS "ORNO Q"
                                                                                                                                , #tmp_SOL_Quot2."PONR - Order line number" AS "PONR Q"
                                                                                                                                , #tmp_SOL_Quot2."RGDT - Entry date" AS "Entry date Q"
                                                                                                                                , RANK() OVER (PARTITION BY #tmp_SOL_Quot1."RGDT - Entry date","Key CO" ORDER BY "Key CO",#tmp_SOL_Quot1."RGDT - Entry date", "Key Q" ASC) AS Rooky
                                                                                                                                , [Key CO]
                                                                                                                                , [Key Q]
                                                                                                FROM   #tmp_SOL_Quot1
                                                                                                                                INNER JOIN #tmp_SOL_Quot2
                                                                                                                                ON #tmp_SOL_Quot1."ITNO - Item number" = #tmp_SOL_Quot2."ITNO - Item number"
                                                                                                                                AND #tmp_SOL_Quot1."CUNO - Customer" = #tmp_SOL_Quot2."CUNO - Customer"
                                                                                                                                AND #tmp_SOL_Quot1."RGDT - Entry date" > #tmp_SOL_Quot2."RGDT - Entry date") TTQ
                                                                WHERE TTQ."Rooky" = 1) TTQ) TTQ
WHERE "Ranking" = 1

[Nolock]

Hi @LucienF38,

in Power BI, there are two data connectivity modes: Direct Query and Import. You said, DQ with an SQL statement is not a solution for you.
But what about using the Import mode with an SQL Statement? The query is then executed on an SQL Server and the results are imported in the column store in Power BI.