SOLVED

Referencing a drop down list in Odata feed url

Occasional Visitor

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

 

 

1 Reply
best response confirmed by likethebeginning (Occasional Visitor)
Solution

Hi @likethebeginning 

 

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

Screenshot.png

 

- 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])
        )
    ),