sql statmant in power bi formula

[LeaRupnik]

Hello, 

i new in power bi and i have many problem. I have sql statment and i woud like to do this in power bi. How do this?

WITH TEMP (PARENT_ID, ID, i) AS
(
SELECT
    PARENT_ID,
    ID,
    1
FROM
    DW.ORGANIZACIJA -- table in db base
WHERE
    PROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO) -- table in db base
UNION ALL
SELECT
    A.PARENT_ID,
    A.ID,
    i + 1
FROM
    DW.ORGANIZACIJA A,-- table in db base
    TEMP T
WHERE
    T.PARENT_ID=A.ID
    AND A.PARENT_ID IS NOT NULL
    AND i < 1000
)

SELECT
    DISTINCT
    'SISTEMIMPOL' CONCAT ORG1.PROGENOT as parent_id,
    ORG1.ORGENOTA AS PARENT,
    'SISTEMIMPOL' CONCAT ORG2.PROGENOT as child_id,
    ORG2.ORGENOTA AS CHILD,
    TEMP.PARENT_ID AS RAZVRSTI_PARENT_ID,
    TEMP.ID AS RAZVRSTI_CHILD_ID
FROM
    TEMP
    INNER JOIN DW.ORGANIZACIJA AS ORG1 ON TEMP.PARENT_ID=ORG1.ID
    INNER JOIN DW.ORGANIZACIJA AS ORG2 ON TEMP.ID=ORG2.ID
ORDER BY
    RAZVRSTI_PARENT_ID,
    RAZVRSTI_CHILD_ID
;

please help me. 

Thanks. 

Lea

[dax]

Hi LeaRupnik,

If you  want to load this data in PowerBI, you could use SQL query directly. If you want to try to use M code to modify it, I think you need to merge two table, then add index and filter it. If possible, could you please inform me more detailed information (such as your sample data and your expected output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LeaRupnik]

Hello. 

i woud like to do tempory table in PowerBi.  At first i greate tem table 

Temp1 = DISTINCT(SELECTCOLUMNS(ORGANIZACIJA;"PARENT_ID";ORGANIZACIJA[PARENT_ID];"ID";ORGANIZACIJA[ID];"i";1)) 

Now i woud luke to ask how in do tempory table include where function from another table like

PROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO)

Thanks. 

LP

LEa