Forum Discussion
ODATA service URL as dynamic parameter
I am in the process of preparing the dashboards in Power BI. The data source is ODATA services from SAP. I am able to successfully get the data from SAP using the ODATA service call in Power BI. Now, i have a requirement. The Business user will change the date in PowerBI dashboard. I have to pass the selected date in the date slicer to the ODATA service URL as dynamic parameter. How to achieve this?
Hi AmarishJayanth
Thanks for reaching out to the Microsoft fabric community forum.
you can create a parameter in power query, then set the parameter with slicer, you can refer to the following link.Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
The following sample cannot allow the user to input the parameter, if you want to edit the parameter to change the filter, you can only do it in power query or edit it in data source setting when the report is published to Service, you can refer to the following link.
Edit parameter settings in the Power BI service - Power BI | Microsoft Learn
Sample data
Step1: Create a parameter in power query
Step 2: Modify the ODATA Service URL:
Open Power Query Editor by navigating to the "Home" tab and clicking on "Transform Data".
Locate the query which retrieves data from the ODATA service.
In the query, replace the static date in the ODATA service URL with the parameter you create. For example:let
Source = OData.Feed("https://your-odata-service-url?date=" & Date.ToText(SelectedDate, "yyyy-MM-dd"))
in
Source
Step3: Load the Data- Close and apply the changes in the Power Query Editor.
- The data will now be filtered based on the selected date in the slicer.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hope this will help.
Thank you.
4 Replies
Hi AmarishJayanth
Thanks for reaching out to the Microsoft fabric community forum.
you can create a parameter in power query, then set the parameter with slicer, you can refer to the following link.Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn
The following sample cannot allow the user to input the parameter, if you want to edit the parameter to change the filter, you can only do it in power query or edit it in data source setting when the report is published to Service, you can refer to the following link.
Edit parameter settings in the Power BI service - Power BI | Microsoft Learn
Sample data
Step1: Create a parameter in power query
Step 2: Modify the ODATA Service URL:
Open Power Query Editor by navigating to the "Home" tab and clicking on "Transform Data".
Locate the query which retrieves data from the ODATA service.
In the query, replace the static date in the ODATA service URL with the parameter you create. For example:let
Source = OData.Feed("https://your-odata-service-url?date=" & Date.ToText(SelectedDate, "yyyy-MM-dd"))
in
Source
Step3: Load the Data- Close and apply the changes in the Power Query Editor.
- The data will now be filtered based on the selected date in the slicer.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hope this will help.
Thank you.
Todate I don't have an idea how to pass a report level parameter to Mcode itself in service, but
you can use Power BI Report Builder to achieve this dynamic parameterization for ODATA services, but it works slightly differently than in Power BI Desktop. Power BI Report Builder is optimized for paginated reports, and parameters are a native feature of these reports. Here's how you can achieve it:I'll try to say this in Steps so that you can try in Power BI Report Builder & let us know the steps you can't achirve:
1. Set Up the Data Source:
- Open Power BI Report Builder.
- Create a new data source connecting to your SAP ODATA service.
- Use the ODATA URL in the connection string and ensure it's working.
2. Create a Dataset:
- Create a dataset using the ODATA data source.
- Include a placeholder for the dynamic date parameter in the ODATA URL. For example:
https://yourSAPServer/odataService?filter=date eq @SelectedDate- Replace @SelectedDate with the parameter you will create.
3. Define a Parameter:
- Go to the Parameters section and create a new parameter:
- Name: SelectedDate
- Data Type: DateTime
- Set the default value (optional).
- Allow the user to select values.
4. Bind the Parameter to the Dataset:
- Edit the query of your dataset to include the @SelectedDate parameter where the date value is needed.
- For example:
https://yourSAPServer/odataService?filter=date eq '@SelectedDate'5. Create a Date Picker in the Report:
- In the report design, the parameter SelectedDate will automatically render as a date picker when the report is run.
- This allows the user to select a date dynamically.
6. Test the Report:
- Preview the report.
- Select a date using the date picker.
- Verify that the ODATA query fetches data for the selected date.
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Good Day to the experts,
The scenario is I want to extract the OData form SAP and I would like to ask that the source URL is required for username and password.
Since the Username and password is keyed correct but there is an error shows we couldn't authenticate this, how can I solve for this?
Also the same URL is works well if using connection string in Excel only, but not in the power query.
Thank you for the time.
