Forum Discussion
Power BI Report Builder: Cannot access report parameters inside Power Query (M language)
Hi everyone,
I am building a paginated report in Power BI Report Builder connected to Google BigQuery using a Power Query data source (M language).
My goal is to execute a BigQuery stored procedure that receives two date parameters entered by the user at runtime.
Here is what I did.
I created two report parameters:
prm_fecha_inicio
prm_fecha_fin
Then I created a new data source by going to:
Data Sources > Get Data > Blank Query
I added the following M query:
let
fechaInicio = Date.ToText(prm_fecha_inicio, "yyyy-MM-dd"),
fechaFin = Date.ToText(prm_fecha_fin, "yyyy-MM-dd"),
Source = GoogleBigQuery.Database([BillingProject="my-project"]),
Database = Source{[Name="my-dataset"]}[Data],
Query = "CALL project.dataset.my_stored_procedure( DATE('" & fechaInicio & "'), DATE('" & fechaFin & "') )",
Result = Value.NativeQuery(Database, Query, null, [EnableFolding=false])
in
Result
Then in Dataset Properties under Parameters I mapped:
prm_fecha_inicio = Format(Parameters!prm_fecha_inicio.Value,"yyyy-MM-dd")
prm_fecha_fin = Format(Parameters!prm_fecha_fin.Value,"yyyy-MM-dd")
The problem is that when I run the report I get this error:
Expression.Error: The import prm_fecha_inicio matches no exports.
I also tried referencing:
prm_fecha_inicio
and
#"@prm_fecha_inicio"
but I get the same error.
It seems that report parameters are not accessible inside the Power Query M code.
My question is:
Is there any supported way to pass report parameters into manually written Power Query code inside Power BI Report Builder?
Or is this scenario not supported?
My final goal is to allow users to enter dynamic dates and execute a parameterized BigQuery stored procedure from a paginated report.
Any help would be appreciated.
Thank you.
Kim_Morales Actually, it is doable, and I have done it many times.
To do this:
1. Create your PQ
2. Add a parameter in PQ (Make sure there is no space). In the attached file, I have a parameter in PQ called StartProductID
3. Add a report parameter, and make sure the report parameter name is the same as the PQ parameter; in this case, it is StartProductID
4. Right-click on your query name under datasets, in the attached report, it is called MyPowerQuery, and select Dataset Properties
5. Under Parameter, select report parameter under parameter value, in parameter name enter the name of the parameter, in this case it is StartProductID
6. Click ok and you are done.
In the attached paginated report, I have two columns in the PQ table: product ID and name, and I'm filtering to show products where product ID is greater than or equal to the report parameter, and it is working.
Let me know if you need further help. Check out the attached paginated report for a full working copy. I have zipped the file because I cannot attach an RDL file.
Hi Kim_Morales ,
Thank you for reaching out to the Microsoft Community Forum.
In Power BI Report Builder, report parameters cannot be referenced directly inside manually written Power Query (M) code that is why you are seeing: "Expression.Error: The import prm_fecha_inicio matches no exports".
Power BI Report Builder has two separate parameter scopes:
1. Report parameters (RDL layer), Created in the report UI Used in Dataset parameter mappings and Expressions (=Parameters!...)2. Power Query (M) parameters, It Used inside M scripts, must be defined within Power Query itself.
Note: These two are not automatically bridged. So when you write Power QueryfechaInicio = Date.ToText(prm_fecha_inicio, "yyyy-MM-dd"), M expects "prm_fecha_inicio" to be a variable defined earlier in M, or a Power Query parameter, but it cannot see the Report Builder parameter, hence the error.
Please try below workaround.
Use Dataset Query Parameters, instead of embedding parameters inside M variables, let the dataset pass parameters into the query. Please modify your M code with below code.
let
Source = GoogleBigQuery.Database([BillingProject="my-project"]),
Database = Source{[Name="my-dataset"]}[Data],Query = "
CALL project.dataset.my_stored_procedure(
DATE(@fechaInicio),
DATE(@fechaFin)
)
",Result = Value.NativeQuery(
Database,
Query,
[
fechaInicio = Date.ToText(@prm_fecha_inicio, "yyyy-MM-dd"),
fechaFin = Date.ToText(@prm_fecha_fin, "yyyy-MM-dd")
],
[EnableFolding=false]
)
in
ResultThen map dataset parameters:
Name Value Expression
@prm_fecha_inicio =Parameters!prm_fecha_inicio.Value
@prm_fecha_fin =Parameters!prm_fecha_fin.ValueNote: The Value.NativeQuery parameter record is the only supported way to inject values into M queries dynamically. Do not try to concatenate strings manually unless absolutely necessary.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
9 Replies
Hi Kim_Morales
No, report parameters are not directly accessible inside manually written M code in report builder. Error occurs because M only recognizes parameters defined within power query environment not report builder parameters.
For paginated reports, approach is to use dataset query parameters and map them through Dataset properties - Parameters allowing connector to pass values to source query.
If you are calling BigQuery stored procedure then using parameterized SQL query or stored procedure call in dataset query itself instead of referencing report parameters in M
Kim_Morales Actually, it is doable, and I have done it many times.
To do this:
1. Create your PQ
2. Add a parameter in PQ (Make sure there is no space). In the attached file, I have a parameter in PQ called StartProductID
3. Add a report parameter, and make sure the report parameter name is the same as the PQ parameter; in this case, it is StartProductID
4. Right-click on your query name under datasets, in the attached report, it is called MyPowerQuery, and select Dataset Properties
5. Under Parameter, select report parameter under parameter value, in parameter name enter the name of the parameter, in this case it is StartProductID
6. Click ok and you are done.
In the attached paginated report, I have two columns in the PQ table: product ID and name, and I'm filtering to show products where product ID is greater than or equal to the report parameter, and it is working.
Let me know if you need further help. Check out the attached paginated report for a full working copy. I have zipped the file because I cannot attach an RDL file.
Hi Kim_Morales ,
Thank you for reaching out to the Microsoft Community Forum.
In Power BI Report Builder, report parameters cannot be referenced directly inside manually written Power Query (M) code that is why you are seeing: "Expression.Error: The import prm_fecha_inicio matches no exports".
Power BI Report Builder has two separate parameter scopes:
1. Report parameters (RDL layer), Created in the report UI Used in Dataset parameter mappings and Expressions (=Parameters!...)2. Power Query (M) parameters, It Used inside M scripts, must be defined within Power Query itself.
Note: These two are not automatically bridged. So when you write Power QueryfechaInicio = Date.ToText(prm_fecha_inicio, "yyyy-MM-dd"), M expects "prm_fecha_inicio" to be a variable defined earlier in M, or a Power Query parameter, but it cannot see the Report Builder parameter, hence the error.
Please try below workaround.
Use Dataset Query Parameters, instead of embedding parameters inside M variables, let the dataset pass parameters into the query. Please modify your M code with below code.
let
Source = GoogleBigQuery.Database([BillingProject="my-project"]),
Database = Source{[Name="my-dataset"]}[Data],Query = "
CALL project.dataset.my_stored_procedure(
DATE(@fechaInicio),
DATE(@fechaFin)
)
",Result = Value.NativeQuery(
Database,
Query,
[
fechaInicio = Date.ToText(@prm_fecha_inicio, "yyyy-MM-dd"),
fechaFin = Date.ToText(@prm_fecha_fin, "yyyy-MM-dd")
],
[EnableFolding=false]
)
in
ResultThen map dataset parameters:
Name Value Expression
@prm_fecha_inicio =Parameters!prm_fecha_inicio.Value
@prm_fecha_fin =Parameters!prm_fecha_fin.ValueNote: The Value.NativeQuery parameter record is the only supported way to inject values into M queries dynamically. Do not try to concatenate strings manually unless absolutely necessary.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Hi Kim_Morales ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh
Hi, I’m really grateful for your response, it has been very helpful.
v-dineshya Don't take me wrong, but I don't understand the reason for your reply when I already provided the solution. Could you please explain why you would use a native query when you can do it without this function? You don't always have SQL as your source.
Hi parry2k ,
Thank you for the feedback.In this scenario, OP calling a BigQuery stored procedure that's the reason i have used the "Value.NativeQuery". Without "Value.NativeQuery" we can't call stored procedures, and cannot control the exact SQL and Parameter passing is indirect and limited. With "Value.NativeQuery" you can explicitly say: “Run this exact query on the source system” and you can control SQL, Stored procedure calls and Parameter binding.
Parameter binding:
Value.NativeQuery(
source,
query,
[ param1 = value1, param2 = value2 ]
)Note: This creates a bridge between Report parameters --> M --> database. "Value.NativeQuery" the only supported way to execute arbitrary commands like stored procedures and pass parameters safely from Report Builder into the source system.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
✅ Why your approach fails
This error:
Expression.Error: The import prm_fecha_inicio matches no exports
happens because:
👉 In Report Builder, Power Query (M) runs inside the data source layer, before report parameters exist.
So:
- prm_fecha_inicio is a report parameter
- M query only sees:
- its own variables
- Power Query parameters (not report parameters)
✅ Therefore:
Report parameters are NOT accessible inside M code
