A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
What is the DATATABLE Function?
The DATATABLE function is a DAX function that creates a new table. This function allows users to define the structure of the new table, including columns and column data types. The DATATABLE function takes two arguments:
1. The first argument defines the column structure of the new table.
2. The second argument defines the data that will populate the new table.
How to Use the DATATABLE Function
To use the DATATABLE function, follow these steps:
1. Open Power BI Desktop and navigate to the “Modeling” tab.
2. Click on the “New Table” button.
3. In the formula bar, type “=DATATABLE”.
4. Define the column structure of the new table. This is done by creating a table expression using the COLUMNS function.
5. Define the data that will populate the new table. This is done by creating a table expression using the ROWS function.
Creating a Table Expression for Column Structure
To define the column structure of the new table, users need to create a table expression using the COLUMNS function. Here is an example of a table expression that defines the column structure of a new table:
=COLUMNS(“Column Name 1”, Data Type 1, “Column Name 2”, Data Type 2, “Column Name 3”, Data Type 3)
In this expression, "Column Name" refers to the name of the column, and "Data Type" refers to the data type of the column. Here is an example of a table expression that defines the column structure of a new table:
=COLUMNS(“Product Name”, STRING, “Product Price”, CURRENCY, “Product Quantity”, INTEGER)
In this expression, we have defined three columns: "Product Name" (which is a string data type), "Product Price" (which is a currency data type), and "Product Quantity" (which is an integer data type).
Creating a Table Expression for Data
To define the data that will populate the new table, users need to create a table expression using the ROWS function. Here is an example of a table expression that defines the data that will populate a new table:
=ROWS({“Product A”, 10, 100}, {“Product B”, 15, 150}, {“Product C”, 20, 200})
In this expression, each set of curly braces defines a row of data for the new table. In this example, we have defined three rows of data, each with three values (i.e., "Product Name", "Product Price", and "Product Quantity").
Combining the Table Expressions
Once users have defined the table expression for the column structure and the table expression for the data, they need to combine the two expressions to create the new table. Here is an example of a complete DATATABLE function that combines the two expressions:
=DATATABLE(
COLUMNS(“Product Name”, STRING, “Product Price”, CURRENCY, “Product Quantity”, INTEGER),
ROWS({“Product A”, 10, 100}, {“Product B”, 15, 150}, {“Product C”, 20, 200})
)
The DATATABLE function is an incredibly useful DAX function for creating new tables. By following the steps outlined in this article, users can define the column structure and data for a new table and combine those expressions to create a new table. With this knowledge, users can take advantage of the power and flexibility of Power BI to create interactive visualizations and reports.