Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

select with direct query

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

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

2 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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