Using a Parameter to connect to Amazon Redshift

 
Hi, Yes, you can use parameters for an Amazon Redshift connection in Power BI, but Power BI’s built-in Redshift connector does not directly support parameterized connections in the UI. However, you can work around this by modifying the Power Query (M) script.

Use Power Query (M) to Parameterize Redshift Connection

1. Create Parameters

   • Go to "Manage Parameters" → "New Parameter".

   • Create the following parameters:

     • ServerPath (Text) → e.g., "your-redshift-cluster.xxxxxx.us-east-1.redshift.amazonaws.com"

     • DatabaseName (Text) → e.g., "your_database"

     • Username (Optional) → "your_username"

     • Password (Optional) → "your_password"

       ---

       2. Modify Power Query (M) Code

          • Go to "Home" → "Transform Data".

          • Open Advanced Editor and modify your Redshift query:

             

            let Server = ServerPath, // Using the parameter Database = DatabaseName, Source = AmazonRedshift.Database(Server, Database) in Source

            If authentication is needed, modify the connection:

             

            let Server = ServerPath, Database = DatabaseName, Source = AmazonRedshift.Database(Server, Database, [CreateNavigationProperties=true, Credential=[Username=Username, Password=Password]]) in Source

             

            Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

EL4BI

Thank you, johnbasha33 ,for your response.

Hi EL4BI,

We would like to check if the solution provided by johnbasha33 has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

Hi EL4BI,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

Hi EL4BI,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

Hi EL4BI,

Based on my understanding, Power BI does not facilitate dynamic query creation through code within the user interface. The recommended and supported approach is to utilize Power Query's Enter Data feature to list your table names and subsequently invoke the function for each table manually.

Here is a workaround that may help resolve the issue:

1. Navigate to Home > Enter Data. Create a column named TableName with values such as: table1, table2, table3. Rename the query to RedshiftTableList.

2. Right-click on RedshiftTableList, then select Add as New Query. This action will create a table of table names. Select the TableName column, then click Add Column > Invoke Custom Function. Choose your function fnGetRedshiftTable and pass TableName as the input. This will result in the addition of a new column containing the tables loaded from Redshift.

3. In the resulting query, each row will contain a [Table] object sourced from Redshift. Click on each cell under the [Table] column, then select To Table → To Query from the ribbon or context menu. Rename each query accordingly (for example, DimCustomer, FactSales, etc.).

If you find our response helpful, please mark it as the accepted solution and provide kudos. This will assist other community members encountering similar queries.

Thank you.

Hi EL4BI,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

Hi EL4BI,

We are following up to check if your query has been resolved. If you have found a solution, kindly share it with the community to help others who may have the same issue.

If our response was helpful, please mark it as the accepted solution and give kudos. This will benefit the wider community.

Thank you.