1

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

1

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.

0

You can use the Query record to pass parameters. If the cell value is stored in the previous step Name, you can add a new step with the fx button in the formula bar and add the following formula:

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

If they have privacy levels enabled, they'll see a privacy level prompt. They can either choose to assign appropriate privacy levels (maybe both Organizational in this case), or they can disable privacy levels in the Options dialog.

1
  • Hello, Alejandro. Thanks for your answer. This was not what I was looking for, though. I was looking for a way to get a cell value so that I can use it in a way you suggested. Thankfully, I found how to do that myself
    – artemb
    Feb 10 '17 at 13:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.