Using an Excel named range as the SQL for a power query

[HumphreyDog]

Hi,

My apologies if this has already been answered but I've spent hours searching and can't find the answer anywhere.

I would like to use an Excel named range as the SQL script for a query in Power Query.  The reason for doing this is that there are a number of changes that can be made in the ”from” part of the script and it’s (in my opinion) easier to change in Excel.

I’ve setup the named range as sql_query and in the advanced editor of Power Query after many different attempts, the best I’ve got is this:

= Sql.Database("MyServer", "MyDatabase",[Query=Excel.CurrentWorkbook(){[Name="sql_query"]}[Content]])

Unfortunately I get:

DataSource.Error: Microsoft SQL: This function doesn't support the query option 'Query' with value 'Table.FromRecords({})'.

Details:

    Table

For reference the SQL that makes up my named range is:

SELECT

AC.DESCR

,SA.S_HEAD

,SU.LOOKUP

,trim(CC.ANLY) as 'User'

,trim(NC.ANLY) as 'Code'

,AD.ADD_LINE

,SU.EMAIL

,BA.SHOE_PAT_NAME

,trim(BA.SHOE_PAT_NUM) SHOE_PAT_NUM

,BA.SHOE_BRANCH

,trim(BA.SHOE_REF_CODE) SHOE_REF_CODE

FROM ZZZ_PAT AC

LEFT OUTER JOIN ZZZ_BLIP SU ON SU.BLIP_CODE = AC.PAT_CODE

LEFT OUTER JOIN ZZZ_BLIP_ANL_CAT SA ON SA.ANL_CAT_ID='20' AND SA.BLIP_CODE=SU.BLIP_CODE

LEFT OUTER JOIN ZZZ_ANLY NC ON  NC.ANL_CAT_ID = SA.ANL_CAT_ID AND NC.ANLY = SA.ANLY

LEFT OUTER JOIN ZZZ_ADD AD ON AD.ADD_CODE = AC.PAT_CODE

LEFT OUTER JOIN ZZZ_CONT CO ON CO.CONT_CODE = AC.PAT_CODE

LEFT OUTER JOIN ZZZ_PAT_ANL_CAT CC ON CC.ANL_CAT_ID='19' AND CC.PAT_CODE = AC.PAT_CODE

LEFT OUTER JOIN ZZZ_SHOE_DETAILS BA ON BA.SHOE_SUB_CODE = '01' AND BA.SHOE_DETAILS_CODE = AC.PAT_CODE

WHERE AC.PAT_CODE LIKE 'S%'

ORDER BY 1 ASC

The parts that get changed in Excel is anything that says ZZZ, so in Excel I’ve got those parts of the script as a concatenated formula referencing a single cell where I can change ZZZ to whatever I need it to be

I’m currently using Excel 2016, but I’m soon to be upgraded to O365 if this makes any difference

Is what I’m trying to even possible and if so would anyone be able to suggest what I’m doing wrong?

I’m a newbie to Power Query so please forgive any naivety.

Any help would be much appreciated

Thank you

[HumphreyDog]

Hi Ibendlin,

Thank you for your reply but I don't quite follow what's in that link, I'm assuming it's the answer by TheRizza you were pointing me to. 

Apologies if I've not been clear but the question in that link was "How to run a SQL query on an Excel table?" where as I'm trying  to run a query (that's in a named range in Excel) on a SQL database and return the results to Excel.

I've amended what's in Advanced Editor to:

let
Source = Sql.Databases("MyServer"),
MyData1 = Source{[Name="MyDatabase"]}[Data],
Query =  [sql_query]
in
MyData1

But this still doesn't work, all I get is a list of all tables in the SQL database.

Thanks

[lbendlin]

Ah, that makes more sense. You need to specify the entire actual SQL query. The query alias is not usable.

[HumphreyDog]

So to confirm that I understand you, it's not possible to use the SQL script that's in the named range, rather I'd have to actually specify the script. So when the ZZZ needs changing I'd have to do that manually, update the Power Query and re-run each time?

[lbendlin · Accepted solution]

either that, or source your SQL query text from elsewhere (like a parameter, or text file on a sharepoint)

[HumphreyDog]

OK, thanks for you advice Ibendlin

[lbendlin]

You seem to be mixing a couple of concepts quite liberally.

Yes, it is possible to query a named range in an Excel sheet via SQL, in the form of "Select * from [named range]"

But - I doesn't really matter if the named range is fed by a SQL query itself. 

And - You are specifying a SQL server and database and then ignoring it in favor of running the SQL query against the Excel workbook itself?

How to run a SQL query on an Excel table? - Stack Overflow