select with direct query

[Anonymous]

Hello,
This sql works in sql server and now I would like  to run it in power BI.
So I thought may be I can somehow run the below query using direct query?
How is it possible please?
It seems I can not just paste this code in the sql statement section of the directquery

create table #tblQuarterlyDate(QuarterKey int, StartMonthDayKey varchar(10))
insert into #tblQuarterlyDate values (1, '0105')
insert into #tblQuarterlyDate values (2, '0405')
...

Select DateKey, [Quarter],Price
,sum(case when right(str(DateKey,8),4)=qd.StartMonthDayKey then Price else null end)
over( order by DateKey) qPrice
from tblFact f join #tblQuarterlyDate qd on f.Quarter=qd.QuarterKey

[TomMartens]

Hey @Anonymous ,

from your post, it seems that there is a DDL part and a DML part. You can use only SELECT ... or EXECUTE PROCEDURE ... statements as a native query.

Because of this, you have to rewrite your SQL code to something like this

SELECT
...
FROM tblFact f join
(SELECT 1 as QuarterKey, '0105' as StartMonthDayKey
UNION
SELECT 1 as QuarterKey, '0105' as StartMonthDayKey
UNION
...
) as qd on f.Quarter=qd.QuarterKey

You also have to be aware that SQL code written in something like SQL Server Management Studio or Azure Data Studio, does not necessarily represent one SQL Object like a view, as often different statements are separated by a semicolon, or should be separated by a semicolon. Sometimes the SQL code is separated into multiple batches by using GO.

What can be used inside Power BI without any modification is a single statement that starts with SELECT.

Regards,
Tom

[Anonymous · Accepted solution]

Hi,
This seems good although now the issue is using your method I would like to add an extra column which is a calculation i.e.

Select DateKey, [Quarter],Price
,sum(case when right(str(DateKey,8),4)=qd.StartMonthDayKey then Price else null end)

over( order by DateKey) qPrice

, CalculationPerformance = Price/qPrice - 1

from tblFact f join #tblQuarterlyDate qd on f.Quarter=qd.QuarterKey

Thank you