Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 :
Projetc 2 :
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
Solved! Go to Solution.
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.
No I get the same error message :
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 :
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?
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 ?
Best regards
Clement
Sorry @v-jingzhang a quick search on google and I found out.
The script is running, I'll tell you if it worked.
Clement
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 ?
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.
Regards,
Jing
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.
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"
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
12 | |
12 |