Forum Discussion
Helper IIntegrating Web API Parameters in Power BI Paginated Reports
Hi RussellBode
The key difference is that Power BI Desktop and Paginated Report Builder handle parameters differently.
In Power BI Desktop, you can pass PAR_SEL_PROJECT directly inside the M query and it will resolve because Desktop supports dynamic parameters in the query editor.But in Paginated Reports, you cannot pass Power Query M parameters directly Report Builder expects you to define Report Parameters and then link them to your dataset query.
How to fix it:
1. Define a Report Parameter
- In Report Builder, create a report parameter (for example @PAR_SEL_PROJECT).
- This will allow the user (or the report action) to pass a value.
2. Modify the Power Query / Data Source in Report Builder
- In the query designer (or dataset properties), replace your hard-coded parameter reference with the @ syntax that Report Builder understands.
Example:
let Source = Json.Document( Web.Contents( "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytic.svcc/RPZ0F… eq '" & @PAR_SEL_PROJECT & "'&$format=json" ) ), Converted = Table.FromRecords({Source}) // expand as before … in Converted3. Notice here:
- In Report Builder you must use the @ParameterName style.
- The parameter value will be substituted at runtime.
4. Bind the Parameter
- Go to Dataset Properties >> Parameters and map your report parameter (@PAR_SEL_PROJECT) to the query parameter you used in the URL.
- This step makes the report parameter flow into the dataset query.
5. Deploy and Test
- When you run the Paginated Report, it will prompt for the project ID (or you can pass it programmatically when embedding).
- The API call will now correctly use the dynamic value.
Super UserHi RussellBode
The key difference is that Power BI Desktop and Paginated Report Builder handle parameters differently.
In Power BI Desktop, you can pass PAR_SEL_PROJECT directly inside the M query and it will resolve because Desktop supports dynamic parameters in the query editor.But in Paginated Reports, you cannot pass Power Query M parameters directly Report Builder expects you to define Report Parameters and then link them to your dataset query.
How to fix it:
1. Define a Report Parameter
- In Report Builder, create a report parameter (for example @PAR_SEL_PROJECT).
- This will allow the user (or the report action) to pass a value.
2. Modify the Power Query / Data Source in Report Builder
- In the query designer (or dataset properties), replace your hard-coded parameter reference with the @ syntax that Report Builder understands.
Example:
let
Source = Json.Document(
Web.Contents(
"https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytic.svcc/RPZ0F… eq '" & @PAR_SEL_PROJECT & "'&$format=json"
)
),
Converted = Table.FromRecords({Source})
// expand as before …
in
Converted3. Notice here:
- In Report Builder you must use the @ParameterName style.
- The parameter value will be substituted at runtime.
4. Bind the Parameter
- Go to Dataset Properties >> Parameters and map your report parameter (@PAR_SEL_PROJECT) to the query parameter you used in the URL.
- This step makes the report parameter flow into the dataset query.
5. Deploy and Test
- When you run the Paginated Report, it will prompt for the project ID (or you can pass it programmatically when embedding).
- The API call will now correctly use the dynamic value.
RussellBodeHello rohit1991 I m getting this error
Query
let
// Reference the parameter value directly, in the correct Power Query form
PAR_SEL_PROJECT = par_SEL_PROJECT, // This assumes the parameter is named PAR_SEL_PROJECT// Construct the URL dynamically using the parameter value
url = "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ0CF89333C75D1A77ED7826QueryResults?" &
"$select=CE_PLAN_ST_DAT_01,KCZ7A54A713DA9CF850D77A13&" &
"$format=json&" &
"$filter=PAR_SEL_PROJECT eq '" & PAR_SEL_PROJECT & "'",// Fetch the data from the URL
Source = Json.Document(Web.Contents(url)),// Convert the JSON response to a table
Converted = Table.FromList(Source[d], Splitter.SplitByNothing(), null, null, ExtraValues.Error),// Expand the JSON data (assumes the response is in a nested structure)
#"Expanded Data" = Table.ExpandRecordColumn(Converted, "Column1", {"results"}, {"Results"}),// Further expand the results if necessary
#"Expanded Results" = Table.ExpandListColumn(#"Expanded Data", "Results"),
#"Expanded Final" = Table.ExpandRecordColumn(#"Expanded Results", "Results", {"__metadata", "KCZ7A54A713DA9CF850D77A13", "CE_PLAN_ST_DAT_01"}, {"Metadata", "ACount", "Adate"}),// Change column types as needed
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Final", {
{"ACount", Int64.Type},
{"Adate", type text},
{"Metadata", type text}
}),// Extract Unix Timestamp and convert to DateTime (if applicable)
#"Extracted Timestamp" = Table.AddColumn(#"Changed Type", "UnixMilliseconds", each Number.FromText(Text.BetweenDelimiters([Adate], "(", ")"))),
#"Converted Date" = Table.AddColumn(#"Extracted Timestamp", "ConvertedDate", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0, [UnixMilliseconds] / 1000)),// Format the Date for better display
#"Formatted Date" = Table.AddColumn(#"Converted Date", "DateString", each Date.ToText(DateTime.Date([ConvertedDate]), "dd/MM/yyyy"))in
#"Formatted Date"i m doing anything wrong here?
it is like checking parameter with in that window if i pass which crteated before with ending 2 it works fine but i can't change realtime value of this.
- RussellBode
This did worked fine 🙂 thank you