how to create temp table with same sample like table on base

[LeaRupnik]

Hello. 

A have a one problem in SQL i now how to solve the case, bat in power bi i a new. 

I hawe a sql statment how to this do in power bi: 

WITH TEMP (PARENT_ID, ID, i) AS -- tempory table
(
SELECT
    PARENT_ID,
    ID,
    1
FROM
    DW.ORGANIZACIJA -- table on the base
WHERE
    PROGENOT IN (SELECT DISTINCT PROGENOT FROM DW.PAKIRANO) -- table on the base
UNION ALL
SELECT
    A.PARENT_ID,
    A.ID,
    i + 1
FROM
    DW.ORGANIZACIJA A, -- table on the 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
;

Thanks. 

Lea

[v-xuding-msft]

Hi @LeaRupnik ,

If  you want to import the data, you could try add the code into "SQL statement" while connecting SQL Server.

If you want to create a new table, you could try the function of SUMMARIZE. Here are some blogs that you can learn from.

best practices using summarize and addcolumns 

Joining tables in Power BI with Power Query and DAX

side effects in dax of the sort by column setting

Best Regards,

Xue Ding

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

[v-xuding-msft · Accepted solution]

Hi @LeaRupnik ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. More people will benefit from here. Thanks in advance.

Best Regards,

Xue Ding

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

[v-xuding-msft]

Hi @LeaRupnik ,

Sorry for late back. You could use CALCULATETABLE function or  FILTER function to add filter conditions like "where" in SQL.

Table 2 = CALCULATETABLE (VALUES('Table'[Country]),'Table'[Product] = "Paseo" )

 

Reference:

https://www.sqlbi.com/articles/filter-vs-calculatetable-optimization-using-cardinality-estimation/

Best Regards,

Xue Ding

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