Power Bi Table Manipulation Functions [14 Examples]

In this Power Bi Tutorial, we will learn the different types of table manipulation functions with the examples in Power Bi. And also we will discuss the below points:

What is power bi Table Manipulation Function
List Of Power Bi Table Manipulation Function
Power Bi Table Manipulation Function Examples

Table of Contents

What is power bi Table Manipulation Function

The Power Bi table Manipulation Function is a function that returns a table or manipulates the existing table.

List Of Power Bi Table Manipulation Function

Here we will see the different types of table functions with their description and syntax.

Function	Description	Syntax
ADDCOLUMNS	This function add calculated columns to the given table or table expression	ADDCOLUMNS(<Table>, <name>,<expressions>[,<name>,<expression>]…)
ADDMISSIGiTEMS	This Function adds a combination of items from multiple columns to a table if they do not already exist.	ADDMISSINGITEMS( [<showAll_columnName>[, <showAll_columnName> [, …. ] ] ], <table> [, <groupBy_columnName> [, [<filterTable>] [, <groupBy_columnName> [, [<filterTable>] [, … ] ] ] ] ] ] )
CROSSJOIN	This function returns a table that contains the cartesian product of all rows from all tables in the argument	CROSSJOIN (<table>,<table>[,<table>]….)
DATATABLE	This function provides a mechanism for declaring an inline set of data values.	DATATABLE (columnName1, datatype 1, columnName2, datatype2,…,{ {value 1, value 2….},{valueN, valueN+1}..})
DETAILROWS	This function evaluates a Detail Rows Expression defined for a measure and returns the data.	DETAILROWS ([Measure])
DISTINCT column	This function returns a one-column table that contains the distinct values from the specified column.	DISTINCT (<column>)
DISTINCT table	This function returns a table by removing duplicate rows from another table or expression.	DISTINCT (<table>)
EXCEPT	This function returns the rows of one table which do not appear in another table.	EXCEPT (<table expression1>,<table expression2>)
FILTERS	This function returns a table of values directly applied as filters to the column name	FILTERS(<column name>)
GENERATE	This function returns a table with the cartesian product between each row in table 1 and the table that result from evaluating table 2, in the context of the current row from table 1.	GENERATE (<table1>, <table2>)
GENERATEALL	This function returns a table with the cartesian product between each row in table 1 and the table that results from evaluating table 2, in the context of the current row in table 1.	GENERATEALL (<table1>,<table2>)
GENERATESERIES	This function returns a single column table containing the values of an arithmetic series.	GENERATESERIES (<startValue>,<endValue>[,<increment value>])
GROUPBY	This function is similar to summarize function. This function does do an implicit calculate for any extension columns that it adds.	GROUPBY (<table>[, <groupBy_ColumnName> [, <groupBy_ColumnName> [, …]]] [, <name>, <expression> [, <name>, <expression> [, …]]])
IGNORE	This function modifies SUMMERIZECOLUMNS by omitting specific expressions from the BLANK/NULL evaluation.	IGNORE (<expression>)
SELECTCOLUMNS	This function adds the calculated column to the given table or expression.	SELECTCOLUMNS (<Table>,<Name>,<scalar expressions>[, <name>,<Scalar expressions>]..)
SUMMARIZE	This function returns a summary table for the requested total over the set of group.	SUMMARIZE (<table>,<groupby_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
SUMMARIZECOLUMNS	This function returns a summary table over a set of groups.	SUMMARIZECOLUMNS (<groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)
TOPN	This function returns the Top N rows of the specified table.	TOPN (<n values>, <table>,<orderBy_Expression>[<order>[, <orderBy_Expression>[<order>]]…])
UNION	This function creates a union or joins a table from a pair of tables	UNION(<table_ expressions1>,<table_expressions2>[,<table expression>]…)
VALUES	This function returns a one-column table that contains the distinct values from the specified table or column	VALUES (<table name or column name>)

Power Bi Table Manipulation Function

Read Power Bi Relationship Functions

Power Bi Table Manipulation Function Examples

Here we will learn different types of table manipulation functions with examples in Power Bi.

So here we will use the US Superstore dataset. And then in Power Bi, I build a dimensiontional data model using Power Query.

Example 1: Using Power Bi ADDCOLUMNS Functions

Here we will see how to use ADDCOLUMNS Function in Power Bi.

As we know, the ADDCOLUMNS() is a tabular function. So, that means it returns a table.

And the tabular function cannot be used in a measure directly, so they have embedded inside the other function in Power Bi.

So here we will create a calculated table, then we will calculate the total profit by using the ADDCOLUMNS (). The function will return a table with the total profit column.

So, click on the modeling tab -> New table in Power Bi ribbon.

Then write the below DAX formula.

add total profit column = ADDCOLUMNS('Fact-Sales',"Total Profit",SUM('Fact-Sales'[Profit]))

Also, read, Power Bi Time Intelligence Function + 30 Examples

Example 2: Using Power Bi CROSSJOIN Function

Here we will see how to use CROSSJOIN Function in Power Bi to bind the two tables of data together

So here we will cross join dim product table and dim customer table using CROSSJION (), which will return a table having the data from both the table.

So click on the Modeling tab-> New Table in Power Bi ribbon.

Then write the below Dax formula to crossjoin the two table.

Crossjoin = CROSSJOIN('Dim-Customer','Dim-Product')

Read Power Bi Text Functions with Examples

Example 3: Using Power Bi DATATABLE Function

Here we will see how to use DATATABLE function in Power Bi.

The Power Bi DATATABLE function creates a data table, by using this function we can define each column name and data type, and then add the data values to it.

So here we will create a user table, having two column name and region with their data type, and then we will add data to name column and region column.

Now create a calculated table, then write the below Dax formula:

User = DataTable("Name", STRING,  
               "Region", STRING  
               ,{  
                        {" User1","South"},  
                        {" User2","East"},  
                        {" User3","West"},  
                        {" User4","West"},  
                        {" User4","Central"}  
                }  
           )

Read Power BI Information Functions

Example 4: Using Power Bi DISTINCT Function

Here we will see how to use the DISTINCT Function in Power Bi.

So here we will count the number of unique product id for each region, for each year.

So, for this we will create a calculated column then write the below DAX formula:

distinct column = COUNTROWS(DISTINCT('Fact-Sales'[Product ID]))

Read Power Bi count function – DAX Examples

Example 5: Using Power bi Except Function

Here we will see how to use Except function in Power Bi.

Here we have two tables, in one table the employee working in this month and in the other table employee working in last month.

Employee in last month:

Employee in this month:

So now we will create a table which will calculate the new employee in this month using except function.

So for this write the below DAX formula:

New emp = EXCEPT('Emp this month','Emp last month')

Read Power BI average function with Examples

Example 6: Using Power Bi FILTERS function

Here we will see how to use FILTERS function In Power Bi.

We will use the below sample data to find the employee in sales and IT.

Now we will create a calculate column, to calculate the no of employee in sales and IT. So for this write the below Dax formula:

Filters Emp = CALCULATE(COUNT('Emp this month'[Department]), FILTERS('Emp this month'[Department]),'Emp this month'[Department] IN { "Sales","IT"})

Read Power BI MAX and MIN function with Examples

Example 7: Using Power Bi GENERATE Function

Here we will see how to use GENERATE Function in Power Bi.

The Power Bi Generate function is the second expression that will evaluate for each row in the first table.

We will use the Fact sales table, dim product table, and dim geography table.

Now we will create a table, in which we will summary the sales by region and product category by using GENERATE().

Generate = GENERATE(SUMMARIZE('Dim-Geography','Dim-Geography'[City]),
     SUMMARIZE('Dim-Product','Dim-Product'[Category],"Sales", 
      SUM('Fact-Sales'[Sales] )))

Read Power BI DAX Logical functions

Example 8: Using Power Bi GROUPBY Function

Here we will see how to use GROUPBY Function in Power Bi.

We will use the Fact sales table to calculate the total sales based on the region by using GROUPBY Function.

So for this, we will create a new table, then write the below Dax formula:

Group by = GROUPBY(ADDCOLUMNS('Fact-Sales',"total sales",SUM('Fact-Sales'[Sales])),
'Fact-Sales'[Region],"total sales",SUMX(CURRENTGROUP(),[Total Sales]))

Read Power BI DAX SUM and SUMX function

Example 9: Using Power Bi SELECTCOLUMNS Function

Here we will see how to use SELECTCOLUMNS function in Power Bi.

So, The Power Bi SELECTCOLUMNS Function simply takes columns from another table and create a new table from them.

Here we will use the Fact sales table, to create another table that contains one column city and country by using SELECTCOLUMNS Function.

So, for this create a calculated table, then write the below Dax formula:

Selected column table = SELECTCOLUMNS('Fact-Sales',"CityCountry", [City]&", "&[Country])

Read Power bi measure examples

Example 10: Using Power Bi SUMMARIZE Function

Here we will see how to use SUMMARIZE Function in Power Bi.

The Summarize function creates a summary of input table grouped by the specified columns. We will use the Fact-sales table, Dim-order and Dim Product table.

So here we will create a table that returns a summary of the fact-sales grouped around the order date and the product category, this result table allows you to analysis over the sales and profit by date and product category.

So for this write the below Dax formula:

Summarize = SUMMARIZE('Fact-Sales' 
      , 'Dim - Order Date'[Date]  
      , 'Dim-Product'[Category] 
      , "Sales", SUM('Fact-Sales'[Sales])  
      , "Profit", SUM('Fact-Sales'[Profit])  
      )

Read Power bi measure count with filter

Example 11: Using Power Bi SUMMARIZECOLUMNS Function

Here we will see how to SUMMARIZECOLUMNS Function in Power Bi.

The Power Bi SUMMARIZECOLUMNS create a summary table for the requested totals over set of groups.

So here we will use the Dim-Order date table, Dimproduct table, and Fact sales table.

Now we will create a new table, in which we will summarize the columns i.e order date columns, product category, and total sales column from sales column. And then write the below Dax formula:

Summarize columns = SUMMARIZECOLUMNS('Dim - Order Date'[Date],'Dim-Product'[Category]," Sales Amount", SUM('Fact-Sales'[Sales]))

Also, read, How to get selected value from Slicer in Power BI

Example 12: Using Power Bi TOPN function

Here we will see to use TOPN Function in Power Bi

The Power Bi TOPN function return the top n rows in the table, and n is the number of rows.

Now we will create a table, in which we will calculate Top 10 sold product name using TOPN function. Then write the below Dax Formula:

Top 10 = 
    TOPN (
        10,
        ADDCOLUMNS (
            VALUES ( 'Dim-Product'[Product Name] ),
            "@Sales Amount", [Total Sales]
        ),
        [@Sales Amount],
        DESC
    )

Read Power BI Measure multiply with examples

Example 13: Using Power BI UNION Function

Here we will see How to use UNION Function in Power bi.

The Power Bi Union() returns a table that contains all rows from the two table expressions. But the table must have the same number of columns,

So here we have two tables new employee table and last month employee table.

Last month employee table:

New employee table:

Now we will create a table, which will return the union of last month employee and new employee. So for this write the below DAX formula:

Union = UNION('Emp last month','New emp')

Read Power bi sort by measure

Example 14: Using Power BI VALUES Function

Here we will see how to use VALUES Function in Power BI.

The values function returns a one-column table that contains distinct values of the specified table or column.

So here we will create a calculated column, which will count the number of unique product id using VALUES().

Write the below Dax formula:

Column = COUNTROWS(VALUES('Fact-Sales'[Product ID]))

Related Posts:

In this Power Bi tutorial, we learned the different types of Power BI table manipulation functions with examples. And also we discuss the below points:

What is power bi Table Manipulation Function
List Of Power Bi Table Manipulation Function
Power Bi Table Manipulation Function Examples.

Bhawana Rathore

Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com