Convert PQ Advanced Editor info to SQL Select Statement

[porter444]

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:

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.

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

 

[porter444]

I figured it out... it was so simple. Posting solution in case others have the same question later.

Select *
From GAINS.dbo.SkulSkul2

 

[Ken Puls]

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.