# 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
At A Glance - Here's what we'll cover:

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

## 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]))``

### 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')``

### 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"}
}
)``````

### 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]))``

### 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')``

### 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"})``

### 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] )))``````

### 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]))``````

### 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])``

### 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])
)``````

### 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]))``

### 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,
VALUES ( 'Dim-Product'[Product Name] ),
"@Sales Amount", [Total Sales]
),
[@Sales Amount],
DESC
)``````

### 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')``

### 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]))``