Using a Parameter to connect to Amazon Redshift

[EL4BI]

Hi Everyone, 

Anyone know how I can connect to Amazon Redshift using a created parameter?  I created two parameters with the server path and database name.  But I can't seem to utilize it when I go to "Get data" and "Amazon Redshift".  I have also tried going to "Data source settings", but the button to "Change Source..." for Amazon Redshift is greyed out.  I have many tables in Redshfit that I want to bring into Power BI and later will need to change the environment (i.e. from Test to Prod) so I don't want to have to do it by hand for every single table.  SQL server connector has the option to allow for you to select the parameter when connecting, but it is not available with the Amazon Redshift connector. 

Am I missing something or is this just not possible?

Thanks

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[johnbasha33]

 
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

[EL4BI]

@johnbasha33 Thank you very much for your help. 
I can make the changes in advance editor using the parameters once I imported the table into Power BI.  If I now want to bring in another table from redshift, how do I utilize the parameters to do that without first bringing it in using the usual way, then making changes in advance editor?  For example, if I need to bring in 50 tables from Redshift, I want to be able to use the parameters to bring them in now that I have created the parameters.  Thank you.  

[johnbasha33]

@EL4BI 
Hi, 

You can leverage Power Query (M language) and parameters to dynamically import multiple tables from Redshift without manually adding them one by one. Here's how you can do it efficiently:

---

Steps to Import Multiple Tables Using Parameters in Power BI

1. Define Parameters for Connection

Since you’ve already created parameters, ensure you have:

• ServerName (Redshift server)

• DatabaseName (Database name)

• SchemaName (Schema if applicable)

TableName (Use this dynamically to switch tables

Create a Function to Load Tables

Instead of manually importing each table, create a function that takes a table name as input and fetches data from Redshift.

Power Query (M) Code for Function

1. Go to Power Query Editor → Click New Source → Choose Blank Query.

2. Rename it to fnGetRedshiftTable

Open the Advanced Editor and replace the code with:
(let TableName as text) =>
let
Source = Odbc.DataSource("dsn=" & ServerName, [HierarchicalNavigation=true]),
Database = Source{[Name=DatabaseName]}[Data],
Schema = Database{[Name=SchemaName]}[Data],
TableData = Schema{[Name=TableName]}[Data] // Dynamically fetch table
in
TableData

Use a List to Import Multiple Tables

1. Create a list of table names you want to import:

   • You can create a table in Power BI with table names.

   • Or manually define them in Power Query.

2. Call the function for each table:

   • Create a new blank query.

Open Advanced Editor and paste the code below:
let
TableNames = {"table1", "table2", "table3"}, // List your table names here
GetTables = List.Transform(TableNames, each fnGetRedshiftTable(_)),
CombinedData = Table.Combine(GetTables) // Combine all tables into one
in
CombinedData

Alternative: Using a Parameter Table

If you have a table with all the Redshift table names, use List.Transform to loop through them dynamically.

let
TableList = ParameterTable[TableName], // Reference your table with table names
GetTables = List.Transform(TableList, each fnGetRedshiftTable(_)),
CombinedData = Table.Combine(GetTables)
in
CombinedData

• Now, every time you refresh, Power BI will automatically pull all tables based on the parameter list.

• This method eliminates the need to manually add tables via the UI before modifying them in the Advanced Editor.

You can also filter specific tables dynamically by modifying the parameter table.

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

[EL4BI]

Thank you @johnbasha33.  This has been very helpful.  

Instead of connecting via an ODBC connection, I set up the function "fnGetRedshiftTable" with parameters and a direct AmazonRedshift connection.

I used the following code for the "fnGetRedshiftTable" function:

(TableName as text) =>
let
Source = AmazonRedshift.Database(RSserver,RSdatabase),
Schema = Source{[Name=RSSchemaName]}[Data],
TableData = Schema{[Name=TableName]}[Data]
in
TableData

Where RSserver, RSdatabase, and RSSchemaName are parameters for the server path, database, and schema name.

I than created a blank query "TableName" with:

let

TableNames = {"table1", "table2", "table3"},

GetTables = List.Transform(TableNames, each fnGetRedshiftTables(_)),

???

Here is my question, as the next step, I don’t want to use “CombineData” to combine these tables together.  They are not tables with similar data, rather, they are fact and dimensional tables.  So instead, I want to just load all the tables in the schema into power query as separate queries.  For this example, it would be table1, table2, and table3 as separate tables.  Is there a way to do this? 

Thank you.