cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aspardo Frequent Visitor
Frequent Visitor

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

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.

 

 

24 REPLIES 24
Baskar Super Contributor
Super Contributor

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

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...

 

 

 

KHorseman Super Contributor
Super Contributor

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

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.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
aspardo Frequent Visitor
Frequent Visitor

Re: 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.

KHorseman Super Contributor
Super Contributor

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

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.

Proud to be a Datanaut!
KHorseman Super Contributor
Super Contributor

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

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?


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
aspardo Frequent Visitor
Frequent Visitor

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

desktop

 

 

KHorseman Super Contributor
Super Contributor

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

OK. The online service is pretty restricted as far as writing complex queries and measures. I just wanted to make sure that you were in the right place to see the tools I was describing.


Did I answer your question? Please mark it as a solution.

Proud to be a Datanaut!
arify Established Member
Established Member

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

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

Untitled.png

 

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 Smiley Happy

aspardo Frequent Visitor
Frequent Visitor

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

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?

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 32 members 773 guests
Please welcome our newest community members: