Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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

  • ImkeF's avatar
    ImkeF
    Community 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.

    • Anonymous's avatar
      Anonymous
      Not 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