Power Query - How to get Record.Field value by name instead of index value?

1
votes

I'm new to Power Query and read enough stuff to get a parameterized query up and running.

In Excel, on a tab named Parameters, I have a range named "myParams" which spans A1:B4. It consists of 2 columns, where A1 = "Parameter Name" and B1 = "Value"

    A                 B
1   Parameter Name    Value
2   User ID           12345
3   Other Thing       foo
4   More Things       bar

My query looks something like this, which works fine:

let
    myParamTable = Excel.CurrentWorkbook(){[Name="myParams"]}[Content],
    pUserID = Record.Field(myParamTable{0}, "Value"),

    Source = Sql.Database("myservername", "mydatabase"),

    GoQuery = Value.NativeQuery(Source,
    "
    SELECT * FROM myTable WHERE UserIdField = @UserID",
    [UserID = pUserID]
    )
in
    GoQuery

If I understand how this works, pUserID is assigned the value in position 0 in a column named "Value" as index counting starts from 0.

How can I get the value by the name "User ID" in column A? I imagine it's something close to

pUserID = Record.Field(myParamTable{"User ID"}, "Value"),

I would like to use the names of parameters instead of index value, as the index value may change in the future if a new field is added to the "myParams" range.

Thanks!

2
excelpowerquerym

2 Answers

1
votes

There are many ways. Here is one using Lists

let
    myParamTable = Excel.CurrentWorkbook(){[Name="myParams"]}[Content],
    fieldList = myParamTable[#"Parameter Name"],
    valueList = myParamTable[Value],
    pUserID = valueList{List.PositionOf(fieldList,"User ID")}  
in
    pUserID
1
votes

You can reference rows by parameter name rather than index number like this.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"User ID" = Source{[#"Parameter Name"="User ID"]}[Value]
in
    #"User ID"

Chris Webb's blog has a good post on this sort of thing here