Forum Discussion
New to BI. Writing SQL queries to parse data. I am obviously missing something.
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.
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.
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?
