Referencing a drop down list in Odata feed url

[likethebeginning]

Hi,

Power query Odata feed url:

= Odata.Feed("https://blahblah/CHANGE_AUDIT_LOG?$filter=relative_time(this_week),null,[Implementation="2.0"])

I would like "this_week" to reference a drop down menu where the url, and thus the data, will change based on the selected time frame from the drop down menu.

Is this possible?

Thanks

[#2 · Accepted solution]

Hi @likethebeginning 

- Name the cell with the drop-down i.e. ParamForQuery

- Edit your existing query (Advanced Editor) to:

a) Add a step with:

    ValueFromDropDown = Table.FirstValue(
        Excel.CurrentWorkbook(){[Name="ParamForQuery"]}[Content]
    ),

b) Revise your Odata feed call so it looks like:

= Odata.Feed("https://blahblah/CHANGE_AUDIT_LOG?$filter=relative_time(" & ValueFromDropDown & "),null,[Implementation="2.0"])

EDIT (to be complete)

- If the value in your drop-down list is of type Number:

    ValueFromDropDown = Text.From(
        Table.FirstValue(Excel.CurrentWorkbook(){[Name="ParamForQuery"]}[Content])
    ),

- If the value in your drop-down list is of type Date:

    ValueFromDropDown = Text.From(
        DateTime.Date(
            Table.FirstValue(Excel.CurrentWorkbook(){[Name="ParamDate"]}[Content])
        )
    ),