none
Project Online - Parameter in Excel OData Connection RRS feed

All replies

  • Hello,

    no data connection in excel (even SQL Server) allows to pass Parameters on the connection itself.

    You may hard-code the Guid value on the connection, but you can't pass parameters like Sheet1!A11.

    This said, if you have a Excel file that runs only on client-side, you can create a macro that changes the connection string :)

    Cheers

    Monday, March 23, 2015 3:14 PM
  • Well, actually you CAN pass a paramter to a query in Power Query:

    Suppose you have a table called "Parameters" somewhere in your Excel file like this:

    key value
    url https://myserver/pwa/_api/projectdata/Projects(guid'04e3ad9c-e75a-e511-944a-00155d6d4802')

    Then you can add a query to that table and use the result as the actual query to the Project data, e.g. like this

    let

        Parameters = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],

        FilteredRows = Table.SelectRows(Parameters, each ([key] = "url")),

        Url = FilteredRows{0}[value],

        Source = OData.Feed(Url)

    in

        Source

    In a similar way you can compute the Url of your OData feed, so it is possible to create dynamic queries with Power Query.

    Remains the question how to get the Parameter from the URL into the file.

    Monday, November 2, 2015 5:23 PM