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 databse2) Write queries in power BI3) 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.

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.

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 :)

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.

When I select from a SQL source in PBI Desktop I always make my queries in the Query Editor like this. I prefer using the advanced editor to write the M# code myself, and use parameters to have my values for Server and Database. I do this for 2 reasons - I can easily create a new query by dublicating one of my other queries and then just replace the T-SQL code and I just have to change 2 values if my data source changes since I just change the parameters and then don't have to change all my queries. let Source = Sql.Database(Server, Database, [Query=" --SQL Query Start SELECT a.DWID_Customer AS 'CustomerKey' , a.CustomerNo AS 'Customer No' , a.Name AS 'Customer Name' , a.CustomerLabel AS 'Customer' , a.CustomerPostingGroup AS 'Customer Posting Group' , a.Country AS 'Customer Country' , a.City + ', ' + a.Country AS 'Customer City' , a.SalespersonCode AS 'Salesperson Code' , a.SalespersonName AS 'Salesperson Name' , a.SalespersonName + ' - ' + a.SalespersonCode AS 'Salesperson' FROM D_Customer a --SQL Query End "]) in Source

Personally, as a SQL person first and foremost who is now working in PowerBI, I would have to say, I take the opposite approach and also advise others to take the opposite approach as well if performance is at all importand. When you do all fo the filtering after you bring in the data, it has a much higher impact on the SQL server every time you refresh the data. Plus, it has to transport everything across the network just to throw it away. My rules personally would be as follows:For a one time data pull - not something that will be regularly refreshing OR if you can't write decent SQL code OR if you database is on a strong server and is in the MB or low GB size and not TeraBytes:1. Connect to SQL as the source from Power BI2. Take the easy way, click all fo the tables that you want.3. Click Edit and make and changes that you want via M (either point and click or with programming)4. Apply and Save5. If you need - add additional calculated columns or measures via DAX.6. Design away If this is a dashboard or report that will be getting refreshed AND can write select statements with SQL1. Open your favorite query writing tool. You can do it in PowerBI, but no Intellisense, no formatting, no easy debugging, etc.2. Write the query and play around with it until you are happy.3. In PowerBI, Use SQL as the source and paste the query that you copied from the other tool. This nice thing is, you can continually refresh and get new data without having to start from scratch. 4. If you need to make changes easier done in M than SQL, make them after importing the query.5. Continue as above. Just my thoughts. I feel sorry for the DBAs trying to maintain performance on their servers as we start hitting them with more and more UserBI apps that are pulling everything on earth just to throw it away after.

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

40 Replies

Anonymous
Not applicable
10 years ago
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.
Anonymous
Not applicable
10 years ago
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
Resident Rockstar
10 years ago
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
  Regular Visitor
  10 years ago
  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.
  - Anonymous
    Not applicable
    10 years ago
    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.

Forum Discussion

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

40 Replies

Related content

Warehouse write activity in a Pipeline

Something Went Wrong

write DAX

PowerApps Write Back to Excel

Write back in Power BI