SQL Query to Power BI

[Anonymous]

Hey @ all

I write the following Initial SQL in Power BI :

SELECT p.value as panelName, i.value, i.ticketInstanceId, t.isRemoved, t.completionTime

INTO #test1

FROM gs_poststream.workflow_category AS c

RIGHT JOIN gs_poststream.ticket_instance AS t ON c.workFlowCategoryId = t.workFlowCategoryId

RIGHT JOIN gs_poststream.ticket_instance_wpf_element AS i ON t.ticketInstanceId = i.ticketInstanceId

FULL OUTER JOIN gs_poststream.ticket_panel_wpf_element AS p on p.panelWpfId = i.panelWpfId

WHERE c.workflowId = 146 OR c.workflowId = 177 OR c.workflowId = 197 OR c.workflowId = 307 OR c.workflowId = 1377

But unfortunaly I cant get the individual tables back.

Power PI shows only this :

I used the same code in Tableau and get another result.

What is doing Power Bi here ?

Thx @ all.

Greetz.

[Anonymous]

Hey.

It works.

Could you explain, what the difference ist between these queries?

Concrete : Why I get two different results because of "Intotest"?

[camargos88]

Hi @Anonymous ,

When you used the temporary table SQL only returned the quantity of records that your SQL returned (you session in SQL Server is active to show the quantity of rows returned per command). If you wanna use temp tables, after your main query, just select the data on the temp table. But your case is not necessary, like @Pragati11  did, just remove the "into #...." command.

Once you removed the command "INTO #...." the SQL returned the dataset to Power BI.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver15

Ricardo

[Pragati11 · Accepted solution]

Hi @Anonymous ,

When you select your query results using "INTO #test", it summarises the resut of this query into this attribute and results in returning number of rows.

But, when you remove "INTO #test", it ends up as a dataset. You are writing a query to specify a dataset in power BI.

For more details on this, refer the following blog:

https://blog.crossjoin.co.uk/2017/06/11/query-folding-and-writing-your-own-sql-queries-in-power-querypower-biexcel-get-transform/

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

[Pragati11]

Hi @Anonymous ,

I am interested to know if this query is working in SQL environment?

The query should be as follows:

SELECT p.value as panelName, i.value, i.ticketInstanceId, t.isRemoved, t.completionTime

FROM gs_poststream.workflow_category AS c

RIGHT JOIN gs_poststream.ticket_instance AS t ON c.workFlowCategoryId = t.workFlowCategoryId

RIGHT JOIN gs_poststream.ticket_instance_wpf_element AS i ON t.ticketInstanceId = i.ticketInstanceId

FULL OUTER JOIN gs_poststream.ticket_panel_wpf_element AS p on p.panelWpfId = i.panelWpfId

WHERE c.workflowId = 146 OR c.workflowId = 177 OR c.workflowId = 197 OR c.workflowId = 307 OR c.workflowId = 1377;

I have removed "INTO #test1". This is not required.

Using above query you end up with a dataset in Power BI with the selected columns in the query.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati