Forum Discussion

Hussard's avatar
Hussard
Frequent Visitor
8 years ago
Solved

SQL Query to Power BI

Hi all,

 

At first I made a view in SQL SERVER, and all is OK.

 

Now I want to create directly into Power BI the same query I've done in SQL Server but from the whole tables.

 

Here is the SQL statement:

 

SELECT pc.id ,count(*) cpt, p.state
FROM Product AS p INNER JOIN Prices AS pp ON p.Id = pp.IdProduct INNER JOIN Catalog AS pc ON p.IdCatalog = pc.Id
WHERE ISNULL(pp.tariff, 0) <> 1 AND pc.version<> - 1 and pc.Status<>2
group by pc.id, p.state
order by pc.id

 

I tried to merge queries in query editor and also create some fields but ... I don't know what is the best method to do that.

 

I think this issue is quite easy to solve but I don't know how.

 

Thanks !

 

Sébastien

Need Help
  • zoloturu's avatar
    zoloturu
    8 years ago

    DirectQuery is not the best mode to get to know Power BI. It is better to work with Import in this case.

     

    How to implement such task via Power BI:

    1. Go to an advanced editor of a blank query.

    2. Reference the first table and filter it as you mentioned in SQL:

        ISNULL(pp.tariff, 0) <> 1 AND pc.version<> - 1 and pc.Status<>2

    3. Repeat the previous action to last two tables.

    4. Use merge to join 1st and 2nd.

    5. Merge result of the previous action with 3rd.

    6. Do grouping

    7. Do ordering

     

    Regards,

    Ruslan

10 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    8 years ago

    Hussard hi  a bit confused about what you are asking?  are you struggling with the approach you have tried is there a problem, if so what is the problem exactly.

     

    are you saying you want to import the whole table into power bi and then apply those conditions?  

     

    can you not just post your query into power bi and use your sql code?

    • Hussard's avatar
      Hussard
      Frequent Visitor
      8 years ago

      Hi,

       

      Sorry I was unclear :(

       

      First, I want to connect my 3 tables in direct query mode. (in my current report I have created a view in SQL Server, and I connect from this view in Power BI. It works, but I want to do the same things directly in Power BI)

       

      How can I merge the data, to have the same result as the query above ?

       

      Sébastien

      • zoloturu's avatar
        zoloturu
        Memorable Member
        8 years ago

        Hi Hussard,

         

        I think you've connected to 3 tables via DirectQuery and you want to create a new 4 table in Power BI, which will be a result of joining of these 3 tables?

         

        Why you simply not connect to a database with specifying your entire query in Advanced options -> SQL statement like below?

         

        dq

         

        Regards,

        Ruslan