Forum Discussion
Using a Parameter to connect to Amazon Redshift
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.
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
-
Go to Power Query Editor → Click New Source → Choose Blank Query.
-
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
-
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.
-
-
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 !!
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
TableDataWhere 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.