New to BI. Writing SQL queries to parse data. I am obviously missing something.

[aspardo]

Started using Power BI on the recommendation of a friend. It looks well made and powerful, so I hope to stick with it.

I am pretty decent with SQL and can write some lengthy queries. My goal is the following:

1) Pull data from out databse

2) Write queries in power BI

3) Publish dashboards / reports based on those queries

I have connected to our database, so step 1 is done.

What I am stuck on is where / how to write actual SQL queries. I want to be able to write a query such as SELECT * FROM shop WHERE X LIMIT 100 and just have it spit out the data, so I can manipulate it, put it into dashboards, and create charts, so later on I can push it to the cloud.

[Seth_C_Bauer]

@aspardo Another option here that hasn't been mentioned, and that I would recommend prior to doing any querying or mashups in Power BI would be to reduce your data set to only the information you need by creating a layer in your SQL database with Views. Then you can just connect to those views from Power BI and you don't have to worry about trying to trim things down after you've already imported or having to deal with writing the query from the editor.

This practice will also be very helpful when adding or modifying your dataset, as you have this additional layer.

[KHorseman]

Generally speaking you do not write SQL queries in Power BI. You can embed them in a query but that's usually not necessary. You build queries in the query editor. Because Power BI can connect to many other sources than SQL it has its own language. When applicable it will translate to SQL behind the scenes on its own. For your example you would connect to your SQL database in the query editor, pick the table named shop, then pick out the columns and add whatever filters you want. Your database will receive the necessary SQL query.

Here are the very basics: https://powerbi.microsoft.com/en-us/blog/pull-data-from-sql-server-with-power-bi-designer/

There are many more resources available online to steer you through the process but if you have specific questions you can always ask here.

[Baskar]

I suggest u go to the Document which we have in power bi site, There u will get the depth knowledge.

If not ask step by step what u want...

[aspardo]

Thank you for the quick reply. My original post it is unclear about what I need. Let me try again.

When I need to acquire data and manipulate it I would do the following:

1) Open frontend such as HeidiSQL

2) Connect to database

3) Query data base, Refine query as many times as needed

4) Pull data into Excel

5) Manipulate data further with Excel.

6) Build charts using data, pivot tables, etc.

From what I understood Power BI would allow me to do the above steps, but all of it contained within Power BI. I want to connect the data base, query and refine my queries, and then get data that I can build some pretty charts and dashboards with power BI.\

I am confused how the query part works exactly.

[KHorseman]

You are nearly correct; you can get the outcome of all of those steps in Power BI, but the steps themselves are different. The query, refining, and manipulation are simply not written in SQL. Power Query has its own language that covers all of the types of connections, queries and transformations it can perform. This includes connecting to a SQL database. You don't ever write any SQL yourself* but rather you build queries in Power BI's query editor. Any SQL code will be passed behind the scenes by Power BI's query parser when necessary. You just hit Get Data and pick your source to connect, then edit the query from there. In your case, Get Data>SQL Server, type in the name of your server, give it credentials when it asks...

When you connect to a SQL Server source in Power BI's query editor, you get a window that allows you to pick a database, then pick a table and then edit the resulting query. If you were to simply load the table without further editing, Power BI would send a SELECT * FROM tablename without you ever writing that. If instead you edit the query and hit Remove Columns, you can pick from a menu only Columns X, Y, and Z. Then say you only want those records where Column X equals 100. You would pick 100 from the filter dropdown from the header of Column X just like if you were filtering a table in Excel. When you load the resulting query, your SQL server should receive a query that reads SELECT 'Column X', 'Column Y', 'Column Z' FROM tablename WHERE 'Column X' = 100

*Technically you can embed your own handwritten SQL query into a source statement in a Power BI query, but don't. I do it sometimes, but only as a last move of desperation when working with a badly designed database. It's not generally recommended. Power Query does something called query folding, which is the process of creating that internally generated SQL statement. Writing your own often results in a less efficient query than letting it do its thing. Also Power Query is a query mashup tool, so if you are mixing together various media (for instance, doing a join between a SQL table and an Excel worksheet, then running part of the result against an Azure Machine Learning services all in the same query) you may make your life more difficult by trying to write your own SQL there.

[KHorseman]

Quick question to make sure I'm not just assuming: you are using the Power BI Desktop program and not just the Power BI website service right?

[aspardo]

desktop

[arify]

If you want to write your SQL queries, it's pretty simple:

However, like KHorseman said, our software does handle a lot of query operations (filter, join, distinct, groupby etc.) you do with buttons and turn them into SQL queries 🙂

[tfamutimi]

How can I do this when joining tables from 2 different databases

[aspardo]

Thank you for these detailed responses. I really do appreciate it.

1) I put the database credentials into the the screenshotted area that you linked. I write the query into the SQL statement area, but not in regular SQ, but Power Query Formula Language?

2) Once that information is pulled into BI I am unable to change the query structure and get new data and I have to start the process over? In other words, I get to put the query in once and go with that.

3) I assume the query can link multiple tables together? Such as SELECT * FROM customers LEFT JOIN account.customer_id_=customers.id WHERE customers. id > 1000

4) KHorseman mentioned that BI can handle a lot of query operations. What potential benefits are there in using the buttons instead of writing the query and getting the exact data I need?

[anithat]

Hi @aspardo

In the Advanced query options write the query in the format that your database accepts. No need to modify it as per the powerBI format.

In general, You might be using some third party tool to execute your queries and check the data. Use the same query in same format - just COPY and PASTE that query in power BI desktop Advanced query options.

PowerBI converts it into its readable format.

Further, at any point if you want to change the query, then

1> Go to Edit Queries

2> Choose the query

3> On the right hand side Double click the 'Source' under Applied Steps

4> Modify the query

5> Save

If you have any further issues, please let me know.

[newpi]

Hi @anithat Curious to know how this works? I'm facing a similar issue. I have built a query in mysql client and want to copy paste this. My questions are:

1) First I have to connect to source and load tables? or

2) Write the query in advanced editor and then connect to source? Confused how it works.

[calerof]

Hi @anithat  and supporting team , @ElizabethTachji ,

I performed your procedure, only modifying the query to the table I need, as follows:

But I'm still getting this error:

Could you please help?

Best regards,

Fernando

[alwweb]

@BB9 

If you are moving all of the required data into a single table in another database using a single query or stored proc, you can simply put that query or execution of that stored procedure into the query option area when you expand Advanced during the import.  This is where a SQL query goes.  After the import screens, when in the Source step and you go into the Advanced Editor in the Power Query Editor, this would be where you would write an M query to limit the data being brought in.

Finally, you could create a view in the same database as the tables and write a SQL statement to retrieve the data from the view.

Having the proper SQL indexes in the source database will help the performance drastically.

Please let me know if any of this helps.

@calerof 

I'm not sure if you saw my earlier response to your question, but you are trying to put an M query where the SQL query is supposed to go.   You would put this query into the Advanced query of the data source after the import has happened when you are in the Power Query Editor, with the large table you want to limit the import highlighted on the left side, and the connection string step selected on the right side, click Advanced Editor as shown below ...

The other option would be to write the SQL statement as the following if using MS SQL Server as the source (other sources have different syntax)

SELECT * --or list out your column names that you want

FROM dbo.RIN1

WHERE DocDate > '20171231';

Please let me know if this helps.  I know it has been a while since the original questions were posted.

[Anonymous]

How can I edit the query when I'm using the BigQuery connector?

The bigQuery connector just allow me to select a table, but I need to make a different sql to correlate 2 tables with several gigabytes of data. The result has only 3800 rows and that is what I want to bring to powerBI.

I cannot change my odbc connections, but I can use the BigQuery connector.

Can I edit the SQL in PowerQuery advanced editor?

Thanks a lot

[newpi]

@Anonymous Not clear if you are asking a question. From what I understand you want to merge two tables using a query in Big Query and bring in only that data right which is 3800 rows. What you can do is write your SQL query in Google BQ Console and then save that data as a view under a project. Then in Power BI, connect to that view instead of those 2 tables.

[Anonymous]

Thank you for the quick answer @newpi .

I know I can proceed as you described. My question is if there is a way do specify the SQL in the connector configuration.

As example, for oracle we can write in advanced editor:

let
Source = Oracle.Database("dbHost/db",[HierarchicalNavigation=true, Query="select field from table where condition"] )
in
Source

Can I do the same with bigquery connector? how? I tried the same but the "Query" parameter does not work 😞

I'm using:

let

   Source = GoogleBigQuery.Database([BillingProject="projectid"])
      {[Name="projectid"]}[Data]
         {[Name="myDataset",Kind="Schema"]}[Data]

             {[Name="myTable",Kind="Table"]}[Data]

in

   Source

Documentation is not clear nor can't find the possible options for the connector

Thanks to all

[alwweb]

@calerof .  At this point in the Process your query needs to be SQL.  It is in M which is used a little later in the process. 

Try the following and replace yourtablename wiht the name of the table that you want to retrieve. Once you have that working, you can go from there to get exactly the data that you want.

SELECT TOP 100  *

FROM yourtablemame;

[calerof]

The problem stated here persists for me, but I managed to solve my issue of not being able to upload on table that didn't appear as follows:

1. The database has more than 10,000 tables
2. In SSMS all the tables are visible
3. I deducted after a very long week of insomnia that the problem was the limit of number of tables read with Get Data, SQL Server
4. So I tried the procedure explained above, with no results
5. Then I tried it with the blank query option, and voila

I think it's still not resolved using Advanced Options, so I'll leave it open for your comments.

Best regards,

Fernando

[KHorseman]

1) No, that SQL statement box is actually for SQL statements as you would normally write, not for the Power Query language (it's called M. I'm just going to call it that now). It's just not generally recommended to use that feature at all. You don't even have to write M language formulas yourself. The buttons generate them. If you feel ambitious you can write them yourself but it's rarely necessary unless you're doing really crazy stuff.

2) You just connect to your database and pick a table. You can mess with the query in the query editor from there. That's where you would do things like pick specific columns or filter rows.

3) Yes, this is done by merging queries in the query editor.

4) Using the buttons does give you the exact data you need. The order of operations is simply different from what you're used to.

I second @Seth_C_Bauer's view recommendation. I wish I had that option in my current situation.