Creating a Table in Power BI Using DAX Table Constructor

There are some functions in DAX that are useful in very specific scenarios. For example, sometimes, you might want to create a table entirely in DAX. If you want to do that, what are your ways and how it is possible? This might be helpful especially in the first days of learning about DAX. Let’s see how table constructor can help you to do that. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Table Constructor

Table constructor is not a function in DAX, it is a set of characters which using those, you can create a table in DAX. Table instructor is always surrounded by {} characters. The syntax of table constructor is simple, It is like below:

{<value1>,<value2>...}

This means value1 will be the value of the first column in the table, value2 would be the value of the second column and etc.

if you want to have more rows, you can separate them with parenthesis () and a comma ,  like this:

{
(value1,value2,...),
(value1,value2,...)
}

Let’s Experiment

Create a new Power BI Desktop file. And then in the Modeling tab, click on New Table.

In the table expression, you can write:

Sample Table = {1}

This will create a table called Sample Table, with one single column called “Value”, and the value in the only row for that would be 1. The Value column automatically takes the data type of the Whole Number.

If you use an expression like this with parenthesis;

Sample Table = {(1)}

You would still get the same output:

If I want a table with two or three rows, I can try this expression:

Sample Table = {1,2,3,4}

As you see, even the comma separates the rows. However, what if we want to have more columns? in that case, you need to use parenthesis to bundle them together in one row. like this:

Sample Table = {(1,2,3,4)}

If you want to have multiple rows and columns in the table, you can write an expression like this:

Sample Table = {(1,2),(3,4)}

This will create a table with two columns: value1, and value 2, and two rows as below;

Values in each cell can be anything, here is another example:

Sample Table = {
    (1,"the first row",DATE(2019,1,1)),
    (3,"the second row",Date(2020,5,12))
    }

And Power BI automatically sets the columns data types to Whole Number, Text, and DateTime;

With the table constructor, you can have different data types in each column, but then, Power BI convert all values to a common data type. Like below example;

Sample Table = {
    (1,"the first row",DATE(2019,1,1)),
    (3,"the second row",12),
    (3,"the second row","something")
    }

Limitations

When you are using constructors, you can put any values you like as the values of cells and rows. However, all rows should have the same number of columns. for example, the below expression won’t work:

Sample Table = {
    (1,"the first row",DATE(2019,1,1)),
    (3,"the second row",),
    (3,"the second row","something")
    }

The second row in this sample is missing the third column:

And that is why you get the error: Each tuple in table constructor must have the same number of columns.

If you want to pass a cell with no value, you can either leave that part blank or use the BLANK() function. Like the below example:

Sample Table = {
    (1,"the first row",DATE(2019,1,1)),
    (3,"the second row",),
    (3,"the second row","something")
    }

Column names are always Value1, Value2, Value3,…. and you cannot change it in the table constructor. You can, however, change it afterwards with just renaming it, or even using SelectColumns function. Data types of columns are defined automatically, which you can then go and change it manually. Because of these two limitations, personally, I’d rather use the Datatable function in DAX which gives us more options and flexibility. I’ll write about it in another blog article.

Summary

Table constructor is a fast and simple way of creating tables in DAX if you need. However, this method has some limitations on the column names and the data types, which makes the Datatable function a better replacement for that if you want more customization.

Video

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 12 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, Power BI Summit, and Data Insight Summit.
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.