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 :)


    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


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

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

        Url = FilteredRows{0}[value],

        Source = OData.Feed(Url)



    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