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

 

newpi's avatar
newpi
Icon for Helper V rankHelper V
6 years ago

Hi sdjensen 
I'm using MYSQL and connecting to the server using ODBC connection. 
I'm very new to power bi and I want to do something similar. Copy paste my query into power bi and load the data. How do I go about it? The syntax i'm using is similar to yours.

Source = Odbc.DataSource("dsn=1accountsdbuni", [Odbc.Query=

--- Query here ----])

 

Syntax works fine but I'm getting following error:

Expression.Error: The provided options are not valid. Details: [List]

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  - my default database name is part of my DSN definition on a page with an option to change the default database. 

     

    If your databasename can't be defined in the DSN, is there a way to set it in the connection string above the SQL statement by using the database= option? 

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    This is now solved. The 4 missing columns data was loading as Binary and I converted it to text type in the query editor and could load all columns

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  So glad you figured that out.  I was not even considering that as one of the troubleshooting steps I was trying to put together for you.  

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  

    Can you tell me what steps you are following to get to the point where you are receiving the error?  I don't have a MySQL to play with, but I may be able to spot where there is a problem.

     

    I assume when you connect to the ODBC source, after selection yoru DSN in the From ODBC window, then you add your query, exactly as it is in the MySQL editor into the SQL statement (optional) box, then get your error when you click OK.  Is there anything else going on?

     

    Have you tried using the MySQL connector?

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    alwweb 
    I'm trying to run a MYSQL query within Power BI. I have connected to MYSQL using an ODBC connector. I was facing issues connecting directly with MYSQL and hence went the ODBC route and that worked for.
    Now all I want to do is run a  MYSQL query I have written with multiple joins and everything within Power BI. The query runs fine and gives me result in a MYSQL client but unable to make it run in Power BI.

  • alwweb's avatar
    alwweb
    Icon for Advocate II rankAdvocate II
    6 years ago

    newpi  Thank you for the additional info.

     

    I just tested an ODBC connection to one of my Microsoft SQL databases (this is all I have right now) and it worked.  I created an ODBC connection that said it was 32/64 bit and a User DSN.  I would assume it needs to match the PowerBI "bitness" and the server bitness.

     

    I then chose Import from ODBC and my DSN and query was as shown in the image below. It connected without any errors. On the security page, I picked Windows security, but make sure that you are connecting with a method supported by MySQL. ODBC connections can be weird, so I'm sorry that I don't have a  MySQL server to test this with to help you out. 

    Please let me know if this helps.

     

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    alwweb Doing the same steps and when I click ok. I get the error of no database selected.
     Whereas If I run without this query, I'm able to connect to the data from the pop up and selecting raw tables.

  • newpi's avatar
    newpi
    Icon for Helper V rankHelper V
    6 years ago

    alwweb Thanks. Just solved this. I just had to modify my query. In MYSQL client I was just writing and doing a select * from table name and that was working fine there but in power bi I had to do Select * from database.tablename and it worked.

    Also, but I'm facing another issue here. I have close 8 columns in my output but in the power bi after connecting with SQL optional statement I'm only seeing first 4 columns and missing the others. This query has multiple left joins so I don't know if that is the issue.

    Power BI Columns

     

    All columns in MYSQL client