Table name change for each new project

Hi clem312 

 

You could first connect to project 1 with the native query in your original post to create a query. Then modify its M codes in Advanced editor like below to convert it into a custom function. 

 

VarAskList and varAskCall are two variables which we use to replace the "AskCall1" and "AskList1" strings in original query codes. I name it as functionQuery.

(varAskList as text, varAskCall as text) =>
let
    Source = Sql.Database("server name", "database name", [Query="SELECT Statistic_Agent.CallID, Lists.dbo." & varAskList & ".AskInterview, Lists.dbo." & varAskList & ".QUOTA7
FROM Lists.dbo." & varAskList & " INNER JOIN
Lists.dbo." & varAskCall & " ON Lists.dbo." & varAskList & ".AskInterview = Lists.dbo." & varAskCall & ".AskInterview INNER JOIN
Statistic_Agent ON Lists.dbo." & varAskCall & ".CallID = Statistic_Agent.CallID", CreateNavigationProperties=false])
in
    Source

 

Create a table which includes table names for 500 projects.

let
    projectList = List.Generate(() => 1, each _ <= 500, each _ + 1),
    #"Converted to Table" = Table.FromList(projectList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Project Number"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Table1", each "AskList"&Text.From([Project Number])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Table2", each "AskCall"&Text.From([Project Number]))
in
    #"Added Custom1"

 

After that, invoke the function functionQuery to add a new column in the previous table. We use the two table name columns for paramters varAskList and var AskCall in the function.

 

After invoking the function, you will see a new table column similar to below. Click the expand icon on the column header to expand the tables. You will have all 500 project data in the query. 

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.