Forum Discussion
Create dynamic reports for multiple values? (Microsoft Training Clarification)
Hi rodneyc8063_1 ,
The custom function uses the columns of the table were you invoke it to populate the parameters of the function.
In this case what you see is that they create a new Sheet 1 where the columns are the SalespersonID
Then they use the values on this table to call the custom function.
So in summary the SalesPersonId you see on the image of the custom function is the column name on the table where you are calling the function.
In fact having an SQL Query on the power query if you replace the filtering on the SQL syntax by a parameter you can in fact use an input from the user (excel or other type) to make the SQL return a different filter from the ones you created the query, this is the same behaviour has you have in any SQL consultation that you can define variable to filter your information the only thing is that Power BI allows you to add the values of those parameters in several different ways using the custom function.
Regarding the image of the folders is just one way or organizing the information if you right click a query you can place it within a folder.
Hi rodneyc8063_1 ,
The custom function uses the columns of the table were you invoke it to populate the parameters of the function.
In this case what you see is that they create a new Sheet 1 where the columns are the SalespersonID
Then they use the values on this table to call the custom function.
So in summary the SalesPersonId you see on the image of the custom function is the column name on the table where you are calling the function.
In fact having an SQL Query on the power query if you replace the filtering on the SQL syntax by a parameter you can in fact use an input from the user (excel or other type) to make the SQL return a different filter from the ones you created the query, this is the same behaviour has you have in any SQL consultation that you can define variable to filter your information the only thing is that Power BI allows you to add the values of those parameters in several different ways using the custom function.
Regarding the image of the folders is just one way or organizing the information if you right click a query you can place it within a folder.
Hi MFelix
Hm - I just wanted to clarify then, Im assuming then that a custom function is not simply limited to one parameter or one column input? Multiple parameters can be used with multiple column inputs?
Thank you for clarifying so far though, that does make more sense 🙂
Correct.
If you look at the syntax of a custom function you will have something similar to:
let Source = (Parameter1 as text) => let Source = ..., Step1 = ... Parameter1, Step2 = ... in Step2 in SourceIn the values in parentesis you can setup something similar to this:
let Source = (Parameter1 as text, Parameter2 as number) => let Source = ..., Step1 = ... Parameter1, Step2 = ... Parameter2 in Step2 in SourceThis way you will have has many parameter has you need and you can place them on the steps where you need them.
Many thanks MFelix !