Advanced Table Manipulation Functions and Other Functions in Power BI

In a previous article, I have explained some table manipulation functions. Here in this article you will learn the rest of the table manipulation functions.
 
Below is the list of some advanced functions that are used to manipulate the table and its columns.
 

Functions and Their Description

 

INTERSECT

 
Returns the rows of left-side table which appear in right side table.
 
Syntax – INTERSECT (LeftTable, RightTable)
 
Tables –Any expression that returns table.
 
Advanced Table Manipulation Functions and Other Functions in Power BI
 

NATURALINNERJOIN

 
Joins the left table with right table using the inner join semantics.
 
Syntax – NATURALINNERJOIN (LeftTable, RightTable)
 
Tables –Any expression that returns table.
 
Note -This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
 

NATURALLEFTOUTERJOIN

 
Joins the left table with right table using the left inner join semantics.
 
Syntax – NATURALOUTERJOIN (LeftTable, RightTable)
 
Tables – Any expression that returns table.
 
Note -This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
 

ROW

 
Returns a single row table with new column specified by the DAX expressions.
 
Syntax – ROW (Name1, Expression1, …)
 
Name1 – Column name in double quotes
 
Expression – Any expression that returns a scalar value.
 
Advanced Table Manipulation Functions and Other Functions in Power BI
 

SELECTCOLUMNS

 
Returns a table with selected columns from the table and new columns specified by the DAX expression.
 
Syntax –SELECTCOLUMNS (Table, Name1, Expression1, …)
 
Table – Any expression that returns a table.
 
Name1 – Name of column in double quote.
 
Expression1 – Any expression that returns scalar value.
 
Advanced Table Manipulation Functions and Other Functions in Power BI
 

TOPN

 
Returns a given number of top rows according to a specified expression.
 
Syntax – TOPN (N_Value, Table, [Orderby_Expression1], [order1], …)
 
N_Value– The number of rows to return
 
Table – Any DAX expression that returns a table of data from where to extract the top 'n' rows.
 
Orderby_Expression1 – Any DAX expression where the result value is used to sort the table and it is evaluated for each row of table.
 
order1 – It is optional. It can 0 or 1. 0 means false or sorts in descending order. 1 means true or ranks in ascending order.
 

TREATAS

 
Treats the columns of the input table as columns from other tables. For each column, filters out any value that are not present in its respective output column.
 
Syntax – TREATAS (Expression, ColumnName1, …)
 
Expression –An expression that results in a table.
 
ColumnName - One or more existing columns.
 

Introduction of Other Functions

 
These functions perform UNIQUE actions that cannot be defined in any of DAX functions categories.
 

Functions and Their Description

 

BLANK

 
Returns a blank.
 
Syntax – BLANK()
 
Advanced Table Manipulation Functions and Other Functions in Power BI
 
Note -Blanks are not equivalent to nulls. Blanks and empty strings ("") are not always equivalent
 

ERROR

 
Raises a user specified error.
 
Syntax – ERROR (ErrorText)
 
ErrorText–A text string containing an error message.
 
Note
  • The ERROR function can be placed in a DAX expression anywhere a scalar value is expected. 
  • This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.