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

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.

FunctionDescriptionSyntax
ADDCOLUMNSThis function add calculated columns to the given table or table expressionADDCOLUMNS(<Table>, <name>,<expressions>[,<name>,<expression>]…)
ADDMISSIGiTEMSThis 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>] [, … ] ] ] ] ] ] )
CROSSJOINThis function returns a table that contains the cartesian product of all rows from all tables in the argument CROSSJOIN (<table>,<table>[,<table>]….)
DATATABLEThis 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}..})
DETAILROWSThis function evaluates a Detail Rows Expression defined for a measure and returns the data. DETAILROWS ([Measure])
DISTINCT columnThis function returns a one-column table that contains the distinct values from the specified column. DISTINCT (<column>)
DISTINCT tableThis function returns a table by removing duplicate rows from another table or expression. DISTINCT (<table>)
EXCEPTThis function returns the rows of one table which do not appear in another table. EXCEPT (<table expression1>,<table expression2>)
FILTERSThis function returns a table of values directly applied as filters to the column nameFILTERS(<column name>)
GENERATEThis 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>)
GENERATEALLThis 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>)
GENERATESERIESThis function returns a single column table containing the values of an arithmetic series. GENERATESERIES (<startValue>,<endValue>[,<increment value>])
GROUPBYThis 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> [, …]]])
IGNOREThis function modifies SUMMERIZECOLUMNS by omitting specific expressions from the BLANK/NULL evaluation. IGNORE (<expression>)
SELECTCOLUMNSThis function adds the calculated column to the given table or expression. SELECTCOLUMNS (<Table>,<Name>,<scalar expressions>[, <name>,<Scalar expressions>]..)
SUMMARIZEThis function returns a summary table for the requested total over the set of group. SUMMARIZE (<table>,<groupby_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)
SUMMARIZECOLUMNSThis function returns a summary table over a set of groups. SUMMARIZECOLUMNS (<groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)
TOPNThis function returns the Top N rows of the specified table. TOPN (<n values>, <table>,<orderBy_Expression>[<order>[, <orderBy_Expression>[<order>]]…])
UNIONThis function creates a union or joins a table from a pair of tables UNION(<table_ expressions1>,<table_expressions2>[,<table expression>]…)
VALUESThis 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]))
 Power Bi ADDCOLUMNS Functions
Power Bi ADDCOLUMNS Functions

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')
Power Bi CROSSJOIN Function
Power Bi CROSSJOIN Function

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"}  
                }  
           )
Power Bi DATATABLE Function
Power Bi DATATABLE Function

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]))
Power Bi Table Manipulation Function
Power Bi Table Manipulation Function

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:

Power bi Except Function
Power bi Except Function

Employee in this month:

Power bi Except Function
Power bi Except Function

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')
Power bi Except Function
Power bi Except Function

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.

 Power Bi FILTERS function
Power Bi FILTERS function

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"})
 Power Bi FILTERS function
Power Bi FILTERS function

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] )))
 Power Bi GENERATE Function
Power Bi GENERATE Function

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]))
Power Bi GROUPBY Function
Power Bi GROUPBY Function

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])
 Power Bi SELECTCOLUMNS Function
Power Bi SELECTCOLUMNS Function

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])  
      )
 Power Bi SUMMARIZE Function
Power Bi SUMMARIZE Function

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]))
Power Bi SUMMARIZECOLUMNS Function
Power Bi SUMMARIZECOLUMNS Function

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
    )
 Power Bi TOPN function
Power Bi TOPN function

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:

 Power BI UNION Function
Power BI UNION Function

New employee table:

 Power BI UNION Function
Power BI UNION Function

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')
 Power BI UNION Function
Power BI UNION Function

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]))
Power BI VALUES Function
Power BI VALUES Function

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