Forum Discussion

mehaboob557's avatar
mehaboob557
Resolver III
9 years ago
Solved

How to write MySQL queries in editor query to fecth data and make visualization ?

Hello All,

 

I am a very new bie to Power BI. Exploring so many about Power BI to use. I am successfully connecting to mysql db and i can fecth tables which is need and i manged the relationship between the tables and made some visuals.

 

My prior experience is in Java, spring , vertx, backboneJs e.t.c. I know MySQL queries to get data in code level.

 

But, i am confused how to use the queries to get data in power BI.

 

For example i want data from 3 table table1, table2 and table3 using some where conditions.

 

My questions are,

 

1. how to write queries for the above 3 tables with conditions in query editor or advance query editor. (send me some mysql query format to write a query)

2. If i wrote the priopr point query, with the help of you. It results seperate table or anything else?

3. If it results seperate table, i can use that table to get visual?

 

Please help me. I searched so many docs. My last hope is this forum, as so many good professions are there. 

 

Thanks a lot in advance. Waiting for your response.

 

 

Need Help
Show and Tell
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi mehaboob557,

     

    You can click Get Data -> MySQL database, then paste the query in the red section below then click OK. It will create a new table.

     

     

    Best Regards,
    Qiuyun Yu

12 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    9 years ago

    Hi mehaboob557,

     

    1. To write MySQL query join three tables with where clauses, you can refer to this sample: Multiple Table Joins with WHERE clause. As this issue is MySQL specific, please post a thread in MySQL forum to get help if you have any further question.

     

    2. In Power BI desktop, when we get data from the MySQL table with above query in red section, it will return a separate table.

     

     

    3. Sure, you can visualize data from this joined results in any visual like other table which you just select without any query specified.

     

    Best Regards,
    QiuyunYu

    • mehaboob557's avatar
      mehaboob557
      Resolver III
      9 years ago

      H v-qiuyu-msft,

       

      Thank you for the response. I worked on mysql queries in my web project. But, in power BI, in query editor, i don't the syntax how to write.


      For example, table1 , table 2 and table3 are in my data set. now i need  3 columns of table 1 and  4 columns of table 2  based on TID.

       

       

      This is the editor am talking about.

       

      If i want to do the query as below in the above editor. How can i do. If i do, it will result a new table?

       

      SELECT `opportunities`.`id`,`opportunities`.`name`,`documents`.`id` as `doc_id`,`documents`.`document_name`,`documents`.`category_id`,`documents`.`doc_type` ,`document_revisions`.`file_ext`,`document_revisions`.`id` as `revision_id` ,`document_revisions`.`revision` FROM `opportunities`
  INNER JOIN `documents_opportunities` ON `documents_opportunities`.`opportunity_id`=`opportunities`.`id` 
  INNER JOIN `documents` on `documents`.`id` = `documents_opportunities`.`document_id`
  LEFT JOIN `document_revisions` on `document_revisions`.`document_id` = `documents`.`id`
  WHERE  `opportunities`.`name`='".$opportunity_name."' AND (`documents`.`category_id`='SCOPE_DOCUMENT' OR `documents`.`category_id`='PRODUCTION_DRAWING' OR `documents`.`category_id`='WORKS_CONTRACT' OR `documents`.`category_id`='WORKING_DRAWING')  
   AND document_revisions.id = (  SELECT id FROM document_revisions 
              WHERE document_revisions.document_id = documents.id  ORDER BY document_revisions.revision desc LIMIT 1)

      Please suggest/help me to understand the query part in Power BI.

       

      Thanks in advance.

      • v-qiuyu-msft's avatar
        v-qiuyu-msft
        Community Support
        9 years ago

        Hi mehaboob557,

         

        You can click Get Data -> MySQL database, then paste the query in the red section below then click OK. It will create a new table.

         

         

        Best Regards,
        Qiuyun Yu