Power BI Report Builder: Cannot access report parameters inside Power Query (M language)

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
Result

Then map dataset parameters:

Name                                Value Expression
@prm_fecha_inicio          =Parameters!prm_fecha_inicio.Value
@prm_fecha_fin              =Parameters!prm_fecha_fin.Value

Note: 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