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

But it is often omitted that if Name  is a table, Argument  could be not a number but a 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?

If

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

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:

How Power Query refers to a table in Excel workbook

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

 

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)

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?

  1. First of all, we do not need to remember a desired parameter row when coding (what if user swapped rows in parameters table?).
  2. Then, we do not need to filter parameters table each time to get a desired value.
  3. 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”:

 

We'll take a record from this table

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

I leaved only needed fileds in this record

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

We will filter this table with the result of previous query

We will filter this table with the result of previous query

The result is a desired record:

Hey-ho, it works!!!

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:

 

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

Share this
SUMPRODUCT() and For Each loops in Power Query: Implementations of List.Accumulate. Part 2
What does it mean to be crossfiltered?

Comments

  1. @Maxim –
    It the below function – tbl can be the name given to a cell or a table structured reference
    Row is a 0 based Row number of the Record
    FldNm is the Column name

    let fGetCellVal=(tbl as text, Row as number,FldNm as text)=>

    let
    Source = Excel.CurrentWorkbook(){[Name=tbl]}[Content],
    Value= Record.Field(Source{Row},FldNm)
    in
    Value
    in
    fGetCellVal

    Typical usage
    FilePath = fGetCellVal(“P”,0,”Value”)
    or
    FilePath = fGetCellVal(“Fpath”,0,”Column1″)

    • Maxim Zelensky
      March 29, 2017 - 19:39

      @sam,
      yes, when we use reference to the single cell – it is ok, but in the other cases we need to know the correct row number.
      If the end user somehow swapped rows order, we’ll miss it.
      Now I prefer to use parameters table with the fields “Name” and “Value”, then transform it to the Record and use parameters as Param[Path], etc.

  2. I have 12 tables like the same [Code],[Name] and [Value] columns (>0.5m mixed rows per table). Cos Merge or Append query so painful for me. Can i use this approach to extract the unique list of [Code] or [Value] from tables. Sorry, I’m just newbie on Power Query. Any suggest so appriciated. Thanks

    • Maxim Zelensky
      October 17, 2016 - 15:01

      Hello Long!
      You need unique list of [Code] (or other field) elements from all 12 tables, without appending each table to others?
      It requires appending or other kind of merging anyway.
      The fastest way with GUI is via “Append queries” -> “Three or more tables”, then select desider tables, and the next step – select desired column (or columns, if you are looking for unique values complex key like [Code]&[Value]) and after right-click on them select “Remove duplicates”

      In raw M it could be written as:

      let
      #"Appended Query" = Table.Combine({Table1, Table2}),
      #"Removed Other Columns" = Table.SelectColumns(#"Appended Query",{"Code"}),
      #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
      in
      #"Removed Duplicates"

      or, if you want to keep other columns too,

      let
      #"Appended Query" = Table.Combine({Table1, Table2}),
      #"Removed Duplicates" = Table.Distinct(#"Appended Query", {"Code"})
      in
      #"Removed Duplicates"

      but you need manually type the list of 12 tables in first step

  3. Is it also possible to pass the Field name as a parameter

Leave a Reply

Your email address will not be published / Required fields are marked *