How to pass parameterized value to OData feed in Excel Power Query

1
votes

I have created a OData service which requires a filter.

This OData service is to be accessed from OData Feed option in Micorsoft Excel - Power Query.

OData URL -:

http://176.0.11.79:8000/sap/opu/odata/sap/Z_SALES_REPORT_TUBES_SRV/et_sales_report_tubesSet?$filter= Spmon eq '20161101'

Now I need to pass the filter value of Spmon '20161101' as a parameter. This value is present in a different sheet in the same excel.

How to change the Query to allow the data to be passed from sheet rather than changing the URL every time.

2
excelodataparameter-passingpowerquery

2 Answers

3
votes

Power Query will fold filters for OData, so you can use the autofilter or add the filter step yourself by adding a new step and adding the following formula through the formula bar:

= Table.SelectRows(PreviousStep, each [Spmon] = '20161101')

If it's from a different sheet that you loaded in a query SheetQuery, it will look like:

= Table.SelectRows(PreviousStep, each [Spmon] = SheetQuery{row_index}[column_name])

You will likely need to set the privacy levels for the OData source and worksheet, or you need to disable Privacy Levels through the Options dialog.

-1
votes

I have found an alternate way of passing filters in Power Query (not directly from any sheet)

This was achieved by adding one more source in the Query and using this as variable in the OData Feed.

    Spmon = "20161001"   , 
    Source = OData.Feed("http://176.0.11.79:8000/sap/opu/odata/sap/Z_SALES_REPORT_TUBES_SRV/
et_sales_report_tubesSet?$filter= Spmon eq '" & Spmon & "'")

This variable (Spmon) can be edited directly in the Advance Editor (Power Query) directly.