Excel – Use parameters in Power Query

microsoft excelmicrosoft-excel-2016power-query

Please help me figure out how to parametrise a Power Query.

I have built a PowerQuery query which queries a webservice for certain data and manipulates it in the right way. Let's say I query a task management system for my today's tasks. A query may look something like:

Web.Contents("http://intranet/tasks?assignee=John", [Content=Text.ToBinary("")]),

It works fine, but I want to share this workbook with this query with my colleagues so that they can use it for their own tasks.

Web.Contents("http://intranet/tasks?assignee={INPUT HERE}", [Content=Text.ToBinary("")]),

Ideally, I want a parameter somewhere called "Assignee" (in a cell of a workbook for example). I want a user of this workbook to be able to input their assignee they want to query against, e.g. Peter, so that when they refresh the data, the Query uses their parameter like this

Web.Contents("http://intranet/tasks?assignee=Peter", [Content=Text.ToBinary("")]),

I wouldn't like them to open the PowerQuery editor and edit the query code because our users are will be scared to death of doing it and will probably make mistakes. A cell in the workbook for selecting the assignee I think is the most usable approach.

Is this possible? If so – how?

Thank you

Best Answer

I found this solution on the internet, which is exactly what I was looking for.

Create a new query, open the advanced editor and paste the following code:

let GetValue=(rangeName) => 
    let
      name = Excel.CurrentWorkbook(){[Name=rangeName]}[Content],
      value = name{0}[Column1]
    in
      value
    in GetValue

Save it and now you have a query-function which you can use in another query like that:

GetValue("Password")

What it will do is look for a range called "Password" in the workbook and take the value of the first cell in that range.

Related Question