Home Functions / Power BI / Power Query Quick Filter for Unique Values in Power Query and Power BI

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]

1	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

1	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]

1	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]

1	= 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]

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

let

Source = #table(type table [Name = text, Department = text, Region=text],{{"John","Sales","AL"},{"Jim","IT","FL"}}),

QueriedRecord = Record.RemoveFields(Source{0},{"Region"})

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

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

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"]

1	[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

(param_table as table, param_name as text) =>

let

fnParam = try param_table{[param=param_name]}[value] otherwise null

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

Comments

sam
March 28, 2017 - 20:19

@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.
long
October 16, 2016 - 06:05

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
sam
October 9, 2016 - 21:30

Is it also possible to pass the Field name as a parameter
- Maxim Zelensky
  October 17, 2016 - 14:39
  
  Hi Sam!
  Could you please explain your trick?
  
  Max