How to create a table using Table Constructor or DATATABLE Function in Power BI DAX

Power BI DAX to create a table using Table Constructor Method or DATATABLE Function

In Power BI, we create a Static or a in Memory table using various methods as discussed below.

1. Table Constructor Method:

In this method, we can create/construct a table using the curly braces, inside which we pass the values for rows, each row in a set of brackets (). By default it takes the Column names “Value1”, “Value2″..”ValueN”.

Example:

TableConstructor = { (7, “James Bond”, DATE(2006,01,01)),

(9, “Gold Fish”, ),

(11, “Lions Gate”,BLANK()) }

Result:

Notes:

Please make sure to pass value for each Column in each Row. In case to Skip, either leave the argument with comma or pass BLANK() as value.

We need to rename the Columns once we constructed the table. Or we can define the Column names using SELECTCOLUMNS method.

TableConstructor =

VAR Tbl_Constr = { (7, “James Bond”, DATE(2006,01,01)),

(9, “Gold Fish”, ),

(11, “Lions Gate”,BLANK()) }

VAR SelColums = SELECTCOLUMNS(Tbl_Constr,

“Id”, [Value1],

“Name”, [Value2],

“ref_Date”,[Value3]

)

RETURN SelColums

Result:

2. Row Method:

This method is similar to the above table constructor method, however use the ROW() Function to construct the table.

Example:

RowTable = { ROW(“Id”,7), 
ROW(“Name”, “JamesBond”),
ROW(“RefDate”,DATE(01,01,2017))}
Result:

In this method we use the DATATABLE() Function to construct the table. In this function we need to define the Data type of each column then we pass the values of the same data type for rows respectively.
Example:
DataTable = DATATABLE(“Id”,INTEGER,“Name”,STRING, “RefDate”,DATETIME, 
        {   {7, “James Bond”, “2006-01-01”},
            {9, “Gold Fish”,0 },
            {11, “Lions Gate”,BLANK()}
        })
Result: