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

[mehaboob557]

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.

[v-qiuyu-msft]

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]

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.

[korayemzd]

@mehaboob557  Try this:

 Source = MySQL.Database("mysqlserver", "databasename", [ReturnSingleDatabase=true, Query="select * from mytable where published=true"])

[v-qiuyu-msft · Accepted solution]

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

[Anonymous]

Thank you so much! saved my life

[mehaboob557]

Hi @v-qiuyu-msft,

Thank you so much. I will try and i will accept the solution once it works for me 🙂

[mehaboob557]

Hi @v-qiuyu-msft

Please help in query part. As suggested by you, i did.

But,  OldGuids=true error is coming while i am writing query directly.

[v-qiuyu-msft]

Hi @mehaboob557,

Please make sure the query can run successfully in MySQL workbench firstly then paste the query into below red section:

Best Regards,
Qiuyun Yu

[aneeshvarghese]

Hi i totally understand how to fetch queries from Mysql. My question is about schedule refresh and creating a live connection in power BI web. How do we do that??

[mehaboob557]

Hi @v-qiuyu-msft ,

I want to know, is there dynamic variable can be passed into query using Power Bi. I mean, if i am selecting any value.. that value to be passed into the query which gives a result in the visualization... is this possible ?

[v-qiuyu-msft]

Hi @mehaboob557,

Do you have any other questions? If you are satisfied with my response, would you please mark a helpful reply as an answer so we can close the thread?

Best Regards,
QiuyunYu

[v-qiuyu-msft]

Hi @mehaboob557,

Power BI provide Query Parameter feature, which can be used to filter data already retrieved in Query Editor. But this query parameter can't be passed to MySQL query. For more information, see:

Deep Dive into Query Parameters and Power BI Templates

Power BI Desktop Query Parameters, Part 1

Best Regards,
QiuyunYu