Table Manipulation Function In Power BI

Introduction

 
Today I am going to explain one more category of DAX functions i.e. table manipulation function. These table manipulation functions return tables and these functions are used to manipulate the table like ADDCOLUMN, SELECT, ROW etc.
 

Functions and Their Description

 

ADDCOLUMNS

 
Returns a table with new columns specified by the DAX expression.
 
Syntax = ADDCOLUNM (Table, Name1, Expression1,....)
 
Table - Any DAX expression that returns a table of data.
 
Name - The name given to the column, enclosed in double quotes.
 
Expression -Any DAX expression that returns a scalar expression, evaluated for each row of table.
 
Table Manipulation Function In Power BI
 

ADDMISSINGITEMS

 
Add the row with empty measure value back.
 
Syntax – ADDMISSINGITEMS ([ShowAll_ColumnName1],.., Table, [GroupBy_ColumnName1], …, [FilterTable1],…)
 
ShowAll_ColumnName1 –It is Optional. A column for which to return items with no data for the measures used. If not specified, all columns are returned.
 
Table –Any existing table.
 
GroupBy_ColumnName1 -A column to group by in the supplied table argument. It is optional.
 
FilterTable1 –Any expression that defines which rows are returned. It is also optional.
 
Table Manipulation Function In Power BI
 

CROSSJOIN

 
Returns a table that is a crossjoin of the specified tables.
 
Syntax – CROSSJOIN (Table1, ….)
 
Tables – Any expression that returns a table or tables to perform joins.
 
Table Manipulation Function In Power BI
 

DATATABLE

 
Returns a table with data defined inline.
 
Syntax –DATATABLE (Name1, Type1, …., data)
 
Name1 – Any column or expression that returns table.
 
Type1 – Data type can be INTEGER, DOUBLE, STRING, BOOLEAN, CURRENCY, DATETIME
 
Data – Set of data value present in an existing table.
 
Table Manipulation Function In Power BI
 

EXCEPT

 
Returns the rows of left-side table which do not appear in right-side table.
 
Syntax – EXCEPT (LeftTable, RightTable)
 
LeftTable – Any expression that returns a table.
 
RightTable - Any expression that returns a table.
 
Table Manipulation Function In Power BI
 

FILTERS

 
Returns a table of the filter value applied directly to the specified column.
 
Syntax – FILTERS (ColumnName)
 
ColumnName - The name of an existing column, using standard DAX syntax. It cannot be an expression.
 
Table Manipulation Function In Power BI
 

GENERATE

 
The second table expression will be evaluated for each row in the first table. Returns the cross join of the first table with these results.
 
Syntax – GENERATE (Table1, Table2)
 
Table1 – Any expression that returns table.
 
Table2 – Any expression that returns table.
 
Table Manipulation Function In Power BI
 

GENERATEALL

 
The second table expression will be evaluated for each row in the first table. Returns the cross join of the first table with these results, including rows for which the second table expression is empty.
 
Syntax – GENERATEALL (Table1, Table2)
 
Table1 – Any expression that returns table.
 
Table2 – Any expression that returns table.
 
Table Manipulation Function In Power BI
 

GENERATESERIES

 
Returns a table with one column, populated with sequential values from start to end.
 
Syntax – GENERATESSERIES (StartValue, EndValue, [IncrementValue])
 
StartValue - The initial value used to generate the sequence.
 
EndValue - The end value used to generate the sequence.
 
IncrementValue – It is optional, default value is 1.
 
Table Manipulation Function In Power BI
 

GROUPBY

 
Creates a summary the input table grouped by the specified columns.
 
Syntax – GROUPBY (Table, [Groupby_columnName1], … , [Name1], [Expression1],…..)
 
Table -Any DAX expression that returns a table of data.
 
GroupBy_columnName - The name of an existing column in the table (or in a related table,) by which the data is to be grouped.
 
Name - The name given to a new column that is being added to the list of GroupBy columns, enclosed in double quotes.
 
Expression - One of the X aggregation functions with the first argument being CURRENTGROUP().
 
Table Manipulation Function In Power BI
 

UNION

 
Returns the union of the tables whose columns match.
 
Syntax – UNION (Table1, …)
 
Table –Any expression that returns table.
 
Table Manipulation Function In Power BI
 

VALUES

 
When a column name is given, it returns a single column table of unique values. When a table name is given, it returns a table with the same columns.
 
Syntax –VALUES (TableNameOrColumnName)
 
TableNameOrColumnName–Any existing table or column
 
Table Manipulation Function In Power BI