Connecting to Amazon Redshift data by writing a query.

[Data_Suji]

Hi, I am trying to connect to Amazon Redshift data by writing a query to join multiple tables with conditions in Power BI. I understand that I can connect to tables and do a merge to achieve the same effect, however wanted to know if there could be a way to connect by writing a query just like how we would with SQL server sources. Based on some research, ODBC connectors could be used, but that does not allow the scheduled refreshes. Any pointers on this would be of great help. Thanks a lot. 

[Data_Suji]

Hi,

Thanks. The update helped. However having some difficulties writing complex queries with multiple joins. I get an error saying native queries not enabled.

[v-cgao-msft]

Hi @Data_Suji ,

Please consider marking a previous helpful answer as solution to end this thread, then open another post describing your problem and steps, and @me. This will help the other members find it more quickly.
Thanks in advance.

Here are some references that may be helpful.

How to Get Your Question Answered Quickly

How to provide sample data

Best Regards,
Community Support Team_Gao

[Data_Suji]

Thank you so much for the reply. However I do not get the option to write a query in my "advanced options" when connecting.

Is there any setting that I should enable?

[v-cgao-msft · Accepted solution]

Hi @Data_Suji ,

Please check the version of Power BI Desktop, If the version is not the latest, please update to the latest version.

Power BI October 2021 Feature Summary | Microsoft Power BI Blog | Microsoft Power BI

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[v-cgao-msft]

Hi @Data_Suji ,

In Power BI Desktop, you can connect to an Amazon Redshift database and use the underlying data just like any other data source in Power BI Desktop. At the same time, in the advanced options, the SQL statement function is provided.

Attached document for reference.

Blog_1

Blog_2

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[HairyDrumroll]

Note that this doesn't seem to be available in Power Query online for Dataflows. Yet.

[HairyDrumroll]

Cancel that, works in Dataflows like this:

Source = Value.NativeQuery(AmazonRedshift.Database("server.name.redshift.amazonaws.com","database_name"), "SELECT * FROM database_name.schema.view_or_table", null, [EnableFolding=true])