Forum Discussion
Create Function that loops on Parameter Values
Hi all,
I've created a Function to invoke a table from a SQL database. The function will load the table and keep only the columns that are listed in an Excel file. (but converted to a list).
I would like to automate this a bit further, As I will be doing this alot more.
How could I add a loop that would iterate all tables from a table/list untill they are all added.
This would mean the parameter value for the function would be a list instead of text value as it is now.
How should I proceed here? Cannot seem to get my head around this.
The code of current function here :
( parameterTableName as text) =>
let
//parameterTableName = "v_ha_last_inpat_haward_curr_ward_nl" ,
ColumnsNeeded = Table.SelectColumns( ColumnNames , parameterTableName ),
#"Renamed Columns" = Table.RenameColumns(ColumnsNeeded,{{parameterTableName, "X"}}),
#"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each [X] <> null and [X] <> ""),
ColumnList = Table.ToList ( #"Filtered Rows" ),
Source = Sql.Database(parameterServerName, parameterDatabase),
Database = Source{[Schema="dbo",Item= parameterTableName]}[Data],
#"Removed Other Columns" = Table.SelectColumns( Database , ColumnList )
in
#"Removed Other Columns"
The Name of the list containing all tables I want to add in PQ.
AllTables
Hope you guys can help!
Maybe ImkeF
Regards,
Robin
2 Replies
- ImkeFCommunity Champion
Hi Anonymous ,
not completely sure about the requirement here:
If you say you want to iterate through a list: What shall happen with the results of each iterated item?: Do you want to create one big table from it (with a column with the list items) or do you want to create multiple queries?
If the latter, that's not possible, I'm afraid.- AnonymousNot applicable
Hi,
thank you for the quick response.
The goal was multiple tables indeed. Guess I'll have to stick to adding them manually table per table.Regards,
Robin