Forum Discussion
sql statmant in power bi formula
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
2 Replies
- dax
Community Support
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 ZhiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- LeaRupnik
Helper III
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 likePROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO)
Thanks.
LP
LEa