Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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" 

 

Need Help
  • edhans's avatar
    edhans
    6 years ago

    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.

5 Replies

  • edhans's avatar
    edhans
    Community Champion
    6 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        edhans
        Community Champion
        6 years ago

        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"])