cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
clem312
Frequent Visitor

Table name change for each new project

Hi,

 

I have this software that creates new tables for each new project.

Main Table is Statistic Agent

For each new project two tables are created :

For example for project 1 :

  • AskList1
  • AskCall1

Projetc 2 :

  • AskList2
  • AskCall2

Etc.

I have 500 projects.

My problem is that I need to link these tables but for any project selected in Power BI in order to make a beautiful dashboard.

 

I need the fields Statistic_Agent.CallID, Lists.dbo.AskCall1.AskInterview, Lists.dbo.AskList1.QUOTA7

 

For project 1 the request is :

SELECT Statistic_Agent.CallID, Lists.dbo.AskCall1.AskInterview, Lists.dbo.AskList1.QUOTA7
FROM Lists.dbo.AskList1 INNER JOIN
Lists.dbo.AskCall1 ON Lists.dbo.AskList1.AskInterview = Lists.dbo.AskCall1.AskInterview INNER JOIN
Statistic_Agent ON Lists.dbo.AskCall1.CallID = Statistic_Agent.CallID

 

I don't how to write the script in Power Query so that it works for the 500 projects !

 

Any idea ?

Clement

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

051701.jpg

 

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. 

051702.jpg

 

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

View solution in original post

14 REPLIES 14
clem312
Frequent Visitor

No I get the same error message :

Capture d’écran 2021-05-17 112542.png

 

Capture d’écran 2021-05-17 112508.png

Can you share the M codes of the function in Advanced editor? And the M codes if you connect to a single project with the original query statement? Go to Home > Query > Advanced Editor, copy all the codes and paste them here. Thanks.

Here is the M code :

 

(varAskList as text, varAskCall as text) =>
let
    Source = Sql.Database("10.33.10.62", [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

 

 

In SQL Management studio, this request works :

 

Capture d’écran 2021-05-17 150845.png

 

Hi @clem312 , can you go to New Source > SQL Server and enter your query in the SQL statement textbox below Advanced options to check whether the Power Query can get the data from the database successfully?

051802.jpg

 

Hi @v-jingzhang 

 

It works if I add "Statistics" as Database :

Capture d’écran 2021-05-18 153958.png

Capture d’écran 2021-05-18 153848.png

Hi @clem312 

Since it works if you add "Statistics" as Database, you could click on Transform data in the preview window to add this query first. Then edit this query in Advanced Editor, replace its Query variable's value in Source step with the value in previous post which has parameters. And add below code before let. After this query is converted into a function, enter some values for the parameters to test this function first. If it works, you can follow steps in my first post.

(varAskList as text, varAskCall as text) =>

 

Regards,

Jing

Hi @v-jingzhang 

 

It seems better but I have to accept a request for every incrementation of table name 

"Do you autorize this native query ?"

Is there a way to say yes for all tables ?

Capture d’écran 2021-05-20 113701.png

 

Best regards

Clement 

clem312
Frequent Visitor

Sorry @v-jingzhang a quick search on google and I found out.

The script is running, I'll tell you if it worked.

 

Clement

clem312
Frequent Visitor

Thank you very much @v-jingzhang it worked. 

clem312
Frequent Visitor

Hi @v-jingzhang 

Just a question. When a table doesn't exist, I have an error in the Load of course. 

How is it possible to upload tables OK and ignore the error ?

 

Capture d’écran 2021-05-22 111826.png

Hi @clem312 , sorry for the late reply.

 

Before expanding the TableContent columns, you could remove the rows which contain Errors. Select this column, go to Home > Remove Rows and select Remove Errors.

052803.jpg

Regards,
Jing

v-jingzhang
Community Support
Community Support

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.

051701.jpg

 

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. 

051702.jpg

 

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

View solution in original post

Thank you v-jingzhang,

 

I must be doing something wrong because I get an error message when expanding TableContent :

"We cannot convert a value of type Record to type Text"

 

Capture d’écran 2021-05-17 101932.png

 

I foolowed the creation of the Query :

= (varAskList as text, varAskCall as text) =>
let
    Source = Sql.Database("10.33.10.62", [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

 

 

Hi @clem312 

If you enter the table names of a project to invoke the function, is it able to bring in data of this project? Let's first check if this function works. 

051703.jpg

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors