cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

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

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


Near SE WI? Join our PUG Milwaukee Brew City PUG
arify Established Member
Established Member

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

  1. Credentials will be entered in the next dialog, not the one in my screenshot. Yes, it'll be regular SQL.
  2. Yes
  3. Yes
  4. UI buttons are just an easier way of doing things, if you prefer writing your own SQL it's also fine Smiley Happy
KHorseman Super Contributor
Super Contributor

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

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.

 


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

Proud to be a Datanaut!
sdjensen Senior Member
Senior Member

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

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

 

/sdjensen
aspardo Frequent Visitor
Frequent Visitor

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

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.

 

You are advisng against filtering the data via SQL before I load the data into Power BI? I should filter the data after it is in Power BI via M or the visual querying tools?

 

 

 

KHorseman Super Contributor
Super Contributor

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

>You are advisng against filtering the data via SQL before I load the data into Power BI?

 

Yes I am, but not strongly. You can do it if you're more comfortable in SQL. However if you're going to use SQL I do recommend doing it the way @sdjensen demonstrated. That's how I do it too, but only when necessary. Basically I only do it when I've determined that my own SQL code will run significantly more efficiently than what Power BI would run on its own. For me that's mostly cases of really weird seven-table joins on multi-column criteria. Otherwise I usually just click buttons and let it do its thing.

 

You can write your own M code if you feel ambitious. There are a few things that can only really be done by writing the code, but they're rare and weird. Any regular query tasks can be accomplished by clicking buttons in the editor. You'll have to learn a little bit of M code if you want to start adding custom columns, but it's easy.

 

Moving from SQL to Power BI's query builder can feel a bit weird because they look very different and the order of operations seems backward. In SQL you tell it to select some columns from a table. In Power Query you tell it to connect to a table and keep the columns you want. The two do the same thing but they're written (or generated via the visual tools) in the opposite order. But that's not really how it runs. In the editor it's just loading a preview for you to play with. As I understand it when the query actually runs it isn't pre-loading an unfiltered table and then filtering it. It actually translates back to a regular SQL select statement. The nice thing about M and the query builder is that you can mashup all sorts of sources, so you can do wacky things like a left outer join of your SQL table with a table off a Wikipedia article. The query is all in one language, but the SQL query still happens behind the scenes.

 

It's worth learning to do things the regular query builder way even if you're going to embed your own SQL in most cases. It can do things that SQL can't, and learning to use it properly will give you a better handle on what's going on in your Power BI models in general.


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

Proud to be a Datanaut!
anithat Regular Visitor
Regular Visitor

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

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.

alwweb Regular Visitor
Regular Visitor

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

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 BI

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

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

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

AFoulk Regular Visitor
Regular Visitor

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


@sdjensen wrote:

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

 


 

Could someone please spell this out easier for me? I am not a SQL guy but really need to go this route with my complex BI model. I have many tables loading (some with millions of rows that I would like to try and filter before they load into query editor first) and many editor steps that I feel could be reduced by SQL coding doing a lot of the joins and filters I need done.

My model times out and fails scheduled refresh during business hours a lot due to everything taxing on the SQL server.

Is there a good beginniners book to SQL I could pick up to maybe learn enough to join and filter as I need too?

ElizabethTachji Regular Visitor
Regular Visitor

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

Hi,

 

Where does the data reside?

 

Elizabeth Tachjian

Analytics Realtime

Elizabeth Tachjian

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,216 guests
Please welcome our newest community members: