Convert PQ Advanced Editor info to SQL Select Statement

porter444

porter444

New member
Joined
Sep 8, 2016
Messages
14
Reaction score
0
Points
0
Location
Birmingham, AL
Excel Version(s)
Excel 2016 MSO 64 Bit
I have a SQL statement I’m trying to build, and I’m stuck. (trying to use PQ to back into a solution)

The basic statement I am trying to use is:

Code: 
Select *
From SkulSkul2

When I try to run that in preview in SQL and it throws an error.

I tried connecting to the table via Excel Power Query ODBC and was able to load the table no problem. In the Excel PQ advanced editor, below is the statement below defines the connection. I noticed that the 3[SUP]rd[/SUP] line identifies the schema, and the 4[SUP]th[/SUP] line identifies the table. My thinking is that I need to include reference to the schema in the SQL.


Code: 
let
    Source = Odbc.DataSource("dsn=DSNNAME", [HierarchicalNavigation=true]),
    GAINS_Database = Source{[Name="GAINS",Kind="Database"]}[Data],
    dbo_Schema = GAINS_Database{[Name="dbo",Kind="Schema"]}[Data],
    SkulSkul2_Table = dbo_Schema{[Name="SkulSkul2",Kind="Table"]}[Data]
in
    SkulSkul2_Table

I don't know enough about SQL to figure out the statement/syntax - hoping someone here in this forum can help.

Thanks in advance!​
 
Last edited by a moderator:
I figured it out... it was so simple. Posting solution in case others have the same question later.

Code: 
Select *
From GAINS.dbo.SkulSkul2

 
Hey there,

Are you using a custom SQL query in the connection dialog then? Microsoft actually recommends not doing this, and just connecting to the table directly with PQ, then running any transformations and filters there. Reason is that as soon as you provide your own SQL query, you break query folding. What that means is that ALL the data gets downloaded, and your Excel has to do the processing vs letting the SQL server do it for you.
 
You must log in or register to reply here.
Back
Top