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.
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.
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.\
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.
Did I answer your question? Please mark it as a solution.
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?