Quick Filter for Unique Values in Power Query and Power BI

There are many ways to get a value from parameters table and then pass it to query. One of this methods uses direct selection of unique parameter name. I think it worth a post.

Items selection: brief reminder

As I described in my first ever post “Absolute and Relative References in Power Query”, when we would like to refer to a single item in a list or a cell in a Power Query table, we can use Name{Argument} syntax: TableName{Row} or ListName{Element} . If Name is a table or list, and Argument is number, then it is simple: we asking for a row or element of such position.

The most commonly used syntax for single cell addressing in tables is

Table{RowNumber}[FieldName]

But it is often omitted that if Name is a table, Argument could be not a number but a record:

Table{Record} as record

Quick filter for unique values

Value passed as Record in this expression works like a filter for a matching field in the table. For example, [empl_name = “John”] .

How it works and what is in it for practical uses?

our table has a column named empl_name

and

row with value “John” in this column could be found,

and

this row is unique (i.e. there is only one row with value “John” in column empl_name , matching to the Record ),

then entire record (i.e. row) will be returned as the result of this expression. In other words, a unique matching record from the table.

But there is one important restriction: if there is no unique matching row in the table, an error is raised.

So, when Table{Record} returns a desired result, it has type of record and we can reach a single item from this record by referring to desired field in it:

Table{Record}[Field]

We can see this method in action when we build query to a table from Excel file. Power Query will create such string of code automatically:

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

How Power Query refers to a table in Excel workbook

Implications

For example, we would like to implement table for passing user-defined parameters to Power Query (I recommend this great post “Building a Parameter Table” by Ken Puls):

myTable = #table(type table [param = text, value = text],{{"Name","John"},{"Department","Sales"}}),
person = myTable{[param = "Name"]}[value],
dept = myTable{[param = "Department"]}[value]

In this example in the first row we create a parameters table with columns param and value that is supposed to be a parameters table for use in other queries.

Hardcoded parameters table (just for sample)

Then we got a parameter’s value by applying “record filter” and selecting desired field ([value]):

Now we can quickly get “Name” parameter’ value from the table above

And “Department” parameter’ value from the table above. Any unique parameter.

What are the benefits in this approach?

First of all, we do not need to remember a desired parameter row when coding (what if user swapped rows in parameters table?).
Then, we do not need to filter parameters table each time to get a desired value.
And also we can check for parameters table integrity – if there will be several rows with the same parameter name, or missing parameter value, or missing field, then we’ll got an error and can handle it.

And, don’t forget that this Table{Record} method has a lot of other uses – when we really need to get a record as a result of expression. Also we can pass a more complex (with 2 or more fields) record as filter.

And Argument record could be a result from other queries. Lets name this query as “QueryRecord”:

let
    Source = #table(type table [Name = text, Department = text, Region=text],{{"John","Sales","AL"},{"Jim","IT","FL"}}),
    QueriedRecord = Record.RemoveFields(Source{0},{"Region"})
in
    QueriedRecord

We’ll take a record from this table

The result of above query is a record with two fields: [Name = “John”, Department = “Sales”] .

I leaved only needed fileds in this record

Then we pass it as an filter argument to other table:

let
    Source = #table(type table [Name = text, Department = text, Date = text],{{"John","Sales","01.01.16"},{"Jim","IT","02.01.16"}}),
    ChType = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    Filtered = try ChType{QueryRecord} otherwise null
in
    Filtered

We will filter this table with the result of previous query

The result is a desired record:

[Name = "John", Department = "Sales", Date = "01.01.2016"]

Hey-ho, it works!!!

Another one “Get parameter” function

And a cherry on the cake – quick parameter selection function. You can easy change it for your needs:

(param_table as table, param_name as text) =>
let
    fnParam = try param_table{[param=param_name]}[value] otherwise null
in
    fnParam

You can also find a complete description of item selection in “Microsoft Power Query for Excel Formula Language Specification” (see “6.4.1 Item Access” on page 59 in August 2015 edition).

Follow me:
Share this: