Search code examples
excelodatapowerquery

Use cell value in Excel to change odata url filter

I'm trying to filter my odata query based on a cell value in an Excel File. The user can select a customer in cell A1 in sheet 'Deliveries'. This customer has to be used as filter in the Odata URL. Currently the URL works like this;

OData.Feed("https://example.software:7048/BCNL/ODataV4/Company('Production')/Deliverylines?$filter=Name_Unloading eq 'Value'"

The 'Value' has to be filled from the cell, is this possible?

I tried to make the query dynamic via ="https://example.software:7048/BCNL/ODataV4/Company('Production')/Deliverylines?$filter=Name_Unloading eq '"& 'Deliveries'!A1 &"'"

But I keep getting Expression or syntax errors.

Solution

  • If you are using powerquery, you can retrieve and then use, later, the value from spreadsheet by creating a named range for the cell and in powerquery using

    NameValue= Excel.CurrentWorkbook(){[Name="aaa"]}[Content]{0}[Column1]