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

Question

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.

Answer 1

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.

Answer 2

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.

Answer 3

You can reference any cell in excel using the Excel.CurrentWorkbook function. I prefer to name the cells and refer to them like below. Then I build the uri. I usually prefer to specify and edit the filters in excel as its easier to change that the mquery code. Technically the below should check if columns_1 and filters_1 are blank and not include in the URI.

   company = Excel.CurrentWorkbook(){[Name="company_1"]}[Content][Column1]{0}
   , service = Excel.CurrentWorkbook(){[Name="report_1"]}[Content][Column1]{0}
   , columns_value = Excel.CurrentWorkbook(){[Name="columns_1"]}[Content][Column1]{0}
   , filter_value = Excel.CurrentWorkbook(){[Name="filter_1"]}[Content][Column1]{0}
   , selected_columns = "$select=" & columns_value
   , filters =  "&$filter=" &  filter_value 
   , uri = "https://api.businesscentral.dynamics.com/v2.0/xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx/Production/ODataV4/Company('" & company & "')/"& service &"?" & selected_columns &  filters 
  , Source = OData.Feed(uri, null, [Implementation="2.0"] )