Forum Discussion

aspardo's avatar
aspardo
Regular Visitor
10 years ago

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 t...
Need Help
aspardo's avatar
aspardo
Regular Visitor
10 years ago

desktop

 

 

arify's avatar
arify
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago

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's avatar
    aspardo
    Regular Visitor
    10 years ago

    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?

    • anithat's avatar
      anithat
      Icon for Resolver II rankResolver II
      10 years ago

      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.

      • calerof's avatar
        calerof
        Icon for Impactful Individual rankImpactful Individual
        7 years ago

        Hi anithat  and supporting team , ElizabethTachji ,

         

        I performed your procedure, only modifying the query to the table I need, as follows:

         

        But I'm still getting this error:

         

        Could you please help?

         

        Best regards,

         

        Fernando

         

         

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      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 Anonymous's view recommendation. I wish I had that option in my current situation.

       

      • sdjensen's avatar
        sdjensen
        Icon for Solution Sage rankSolution Sage
        10 years ago

        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

         

    • arify's avatar
      arify
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago
      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 :)
  • tfamutimi's avatar
    tfamutimi
    New Member
    4 years ago

    How can I do this when joining tables from 2 different databases