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.
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>) |
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')
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:
- Power BI Measure Date
- Power bi measure divide
- Power BI Measure Filter
- Power bi measure switch statement with examples
- Countif function in Power BI Measure
- Power Bi Filter Functions with Examples
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 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