cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ko Frequent Visitor
Frequent Visitor

Edit direct query against Amazon Redshift

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" 

 

1 ACCEPTED SOLUTION

Accepted Solutions
edhans Super Contributor
Super Contributor

Re: Edit direct query against Amazon Redshift

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.

View solution in original post

5 REPLIES 5
edhans Super Contributor
Super Contributor

Re: Edit direct query against Amazon Redshift

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

Re: Edit direct query against Amazon Redshift

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

edit SQL Server .PNG

 

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

 

edit RedShift.PNG

edhans Super Contributor
Super Contributor

Re: Edit direct query against Amazon Redshift

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

Re: Edit direct query against Amazon Redshift

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

Re: Edit direct query against Amazon Redshift

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.

View solution in original post

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)