Friday, April 15, 2022

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:


3. DATATABLE Method:
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:


4) Using M-Query:
We can create a dummy table using M-Query as shown below.
[Measures] =

let 
  Source = Table.FromRecords({ [Default = null] })
in
  Source

Result:

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog