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
1 |
Table{RowNumber}[FieldName] |
But it is often omitted that if Name is a table, Argument could be not a number but a 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?
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:
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:
1 |
= Excel.CurrentWorkbook(){[Name="Parameters"]}[Content] |
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):
1 2 3 |
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.
Then we got a parameter’s value by applying “record filter” and selecting desired field ( [value]):
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”:
1 2 3 4 5 |
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 |
The result of above query is a record with two fields: [Name = "John", Department = "Sales"] .
Then we pass it as an filter argument to other table:
1 2 3 4 5 6 |
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 |
The result is a desired record:
1 |
[Name = "John", Department = "Sales", Date = "01.01.2016"] |
Another one “Get parameter” function
And a cherry on the cake – quick parameter selection function. You can easy change it for your needs:
1 2 3 4 5 |
(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).
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