Using a Parameter to connect to Amazon Redshift

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 !!