In another blog article, I explained how easy is to use table constructor in DAX to create a data table fast in Power BI. However, that method has some limitations, such as not being able to name columns or set their data types. In this blog post, I’ll explain about DataTable function in DAX which is giving us more flexibility to set column specific structure. Let’s see how this can be used. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
DataTable Function
Datatable is a function in DAX to create a table. The syntax allows you to define each column name and data type, and then add data values to it. Here is the structure of a Datatable function usage:
Sample Table = DATATABLE(
"column 1 name",<data type>,
"column 2 name",<data type>,
{
{<value row 1 col 1>,<value row 1 col 2>},
{<value row 2 col 1>,<value row 2 col 2>}
}
)
The minimum things you need for this function to work is at least one column, plus one row, which can be used like this:
Sample Table = DATATABLE(
"First Name",STRING,
{
{"Reza"}
}
)
The part inside {} is the data rows. and each row itself is in another {}. So, if I want to add two rows, it would be like this:
Sample Table = DATATABLE(
"First Name",STRING,
{
{"Reza"},
{"Leila"}
}
)
To add more columns to this table, you can just add another line of name and data type, before the first {}. similar to below;
"First Name",STRING,
"Last Name",STRING,
{
{"Reza"},
{"Leila"}
}
)
However, the above expression won’t work like that, because in every row, we still have one value.
You need to have the same number of values in each row. however, blank itself is also considered as value. Blank can be represented with BLANK() or with no value in a place holder.
Sample Table = DATATABLE(
"First Name",STRING,
"Last Name",STRING,
{
{"Reza","Rad"},
{"Leila","Etaati"},
{"someone",},
{"Unknown",blank()}
}
)
You can see that both 3rd and 4th rows are blank in their “Last Name” column, but defined differently in the expression.
The data type of each column can be one of the below types: Integer, Double, String, Boolean, Currency, and DateTime
If a value is specified in a data value which is not of the data type of the column, the type conversion would happen. like what you see below that happens on the “0” in the 3rd column of the first row;
Sample Table = DATATABLE(
"First Name",STRING,
"Last Name",STRING,
"Time",DATETIME,
{
{"Reza","Rad",0},
{"Leila","Etaati",},
{"someone",,"2019-2-10"},
{"Unknown",blank(),"2019-2-10"}
}
)
You can specify column names with special characters in it, such as below. However, if you want to use ” (double quote) in your column name, you need to use another double quote as an escape character for it.
Sample Table = DATATABLE(
"1First Name",STRING,
"@Last Name",STRING,
"""Time",DATETIME,
{
{"Reza","Rad",0},
{"Leila","Etaati",},
{"someone",,"2019-2-10"},
{"Unknown",blank(),"2019-2-10"}
}
)
Adding Expression-based Columns
If you want to add expression-based columns to your table, The best would be using other functions such as SelectColumns, AddColumns, or SummarizeColumns (Depends on what exactly you want to do). Here is an example of adding a Full Name column to our sample table:
Sample Table =
ADDCOLUMNS(
DATATABLE(
"First Name",STRING,
"Last Name",STRING,
"Time",DATETIME,
{
{"Reza","Rad",0},
{"Leila","Etaati",},
{"someone",,"2019-2-10"},
{"Unknown",blank(),"2019-2-10"}
}
),
"Full Name",[First Name]&" "&[Last Name]
)
Limitation
If you use Table Constructor to build a table, you can use any expression as the value. Here is an example that uses the time now() function as a value in a data cell using table constructor:
Table = {(now())}
However, you cannot use that in Datatable function;
Sample Table =
DATATABLE(
"Sample Time",DATETIME,
{
{now()}
}
)
You will get the error: The tuple at index ‘1’ from the table definition of the DATATABLE function does not have a constant expression in the column at index ‘1’.
The value in each cell can be only constant, not any scalar expression.
An alternative for that would be using SelectColumns, AddColumns or any other methods to get an expression-based column added to the table.
Another Method: Table Constructor
In another blog post, I have explained an easier and faster way of creating a table using Table Constructor in DAX for Power BI. If the column names and data types are not important for your scenario, you can follow that approach.
Summary
There are different ways to create a table in Power BI using DAX. Using Datatable function will give you flexibility in defining each column name and data type specifically while the table constructor is just an easy and fast way of creating a data table with no specific metadata setup. The syntax of Datatable function is explained in the screenshot below:
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.