Using DataTable DAX Function for Creating Structured Table in Power BI

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

The minimum things you need for this function to work is at least one column, plus one row, which can be used like this:

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:

To add more columns to this table, you can just add another line of name and data type, before the first {}. similar to below;

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.

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;

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.

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:

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:

However, you cannot use that in Datatable function;

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:

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply

Your email address will not be published. Required fields are marked *