Edit direct query against Amazon Redshift

[ko]

Would like to load data from Amazon Redshift in direct query mode and edit the initial query before loading into PowerBI.

From an old blog post announcing the connectivity (powerbi.microsoft.com/fr-fr/blog/announcing-support-for-amazon-redshift-reports-in-the-power-bi-service) it shows a screen shot where after connecting with server, database, username and pasword you have option to "Load / Edit / Cancel"  Would like the 'Edit'  option but only see "Load/Transform Data/Cancel" 

[edhans]

Transform has replaced "edit"

It is the same thing. They are just harmonzing the terms across a number of platforms. I'm reading a PQ book that says "edit" in every step, and it was published less than a year ago. It is a recent change.

[ko]

Thanks, sorry I have described the wrong symptoms.    With a SQL server source , direct query, local or on cloud , can go to 'Edit Queries'  select 'Source'  presented with dialog box that includes Server, Database and ability to edit the SQL statement

Using Amazon Redshift - the option to Edit the SQL statement does not exist.

[edhans]

Adding direct SQL statements to the Advanced area is a security issue. I recently had an issue where someone was trying to do this with SQL and it ran locally, but not over the gateway, because the gateway permissions didn't have the elevated permisions necessary to run a native statement. Think about it - you could type in a statement to change/remove records or tables.

So perhaps it is a security issue that Amazon RedShift doesn't allow typing in the statements through apps, or Power Query simply doesn't support it at this stage.

You could try connecting to a table normally, then changing the SOURCE statement manually in Power Query. It is possible the functionality is there, but the UI isn't.

A manaual SQL statement looks like this:

= Sql.Database("localhost", "AdventureWorks2017", [Query="select * from Person.ContactType"])

[ko]

I tried manually entering the SQL through advanced editor.  Gave error

Expression.Error:  'Query' is not a valid Amazon Redshift option.  Valid options are : 'BatchSize'

[edhans · Accepted solution]

I'm betting it is a permission issue or a feature they don't support. You may need to check forums that specialize in RedShift.