Create A DataFlow from a SQL Query

[mohammed_essam]

How can I pull data from database datasource using SQL Query instead of pick one single table or the view from the object list, basically, I would like to pull data from mulitiple tables using the same DataFlow ? how can I achive that ? 

[v-yingjl]

Hi @mohammed_essam ,

When you create dataflow using sql database, you can write your custom sql queries here for your need to get specific data:

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohammed_essam]

Ok, can you please give me an example, also do this will work if I am using Oracle datasource?

[v-yingjl · Accepted solution]

Hi @mohammed_essam ,

When I connect to sql, I can write a simple sql statement to get specific table data:

In dataflow, oracle database doe not have the avdanced options to write sql statement, but you can create a balnk query in dataflow using the above query statement to get data:

Query should be like this:

let
  Source = Oracle.Database("server-name", , [Query = "your sql statement"])
in
  Source

Please refer this document about Oracle connector in power query: Oracle.Database 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohammed_essam]

Thank you !!, that works but after I replaced the two commas in the middle with only one single comma, do you know why your script includes two commas in the middle? can you give more more details if that will will work also if I used a complexed query ? more information will be helpful.

[v-yingjl]

Hi @mohammed_essam ,

Sorry, that's my wrong to add an extra comma in the oracle connector grammar when posting to confuse you...

Basically in this connector function, there are only two parameters as the document mentioned. If you use a complexed query, as long as it conforms to the sql specification, it should work.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mahoneypat]

When you make a new query, in the window where you put the server and database, you can expand the Advanced Options fields to directly enter an SQL query.

https://www.red-gate.com/simple-talk/sql/bi/power-bi-introduction-working-with-sql-server-data-in-po...

Regards,

Pat

[mohammed_essam]

Thank you, does this will also work if I am using Oracle datasource, or just working with MS SQL ?